# Security filters

ns_register_filter preauth GET /acquire/upload checkrole Upload
ns_register_filter preauth POST /acquire/upload checkrole Upload
ns_register_filter preauth GET /acquire/scan checkrole Scanner
ns_register_filter preauth POST /acquire/scan checkrole Scanner
ns_register_filter preauth GET /file/* checkrole Viewer
ns_register_filter preauth POST /file/* checkrole Viewer 
ns_register_filter preauth GET /file/*/* checkrole Metadata
ns_register_filter preauth POST /file/*/* checkrole Metadata
ns_register_filter preauth GET /admin* checkrole Admin
ns_register_filter preauth POST /admin* checkrole Admin
ns_register_filter preauth GET /workflow/* checkrole Viewer
ns_register_filter preauth POST /workflow/* checkrole Viewer 

# Catch bad methods

ns_register_proc HEAD /* returnnothing 200
ns_register_proc PUT /* returnnothing 200
ns_register_proc DELETE /* returnnothing 200

# Registered URLs

ns_register_proc GET /login login
ns_register_proc POST /login login

ns_register_proc GET /logout logout
ns_register_proc POST /logout logout

ns_register_proc GET /changepw changepw
ns_register_proc POST /changepw changepw

ns_register_proc -noinherit GET / index

ns_register_proc GET /admin/ adminindex

ns_register_proc GET /admin/user userindex

ns_register_proc GET /admin/user/new newuser
ns_register_proc POST /admin/user/new newuser

ns_register_proc GET /admin/user/update updateuser
ns_register_proc POST /admin/user/update updateuser

ns_register_proc GET /admin/user/role/new adduserrole
ns_register_proc POST /admin/user/role/new adduserrole

ns_register_proc GET /admin/user/role/delete deluserrole
ns_register_proc POST /admin/user/role/delete deluserrole

ns_register_proc GET /admin/filetype filetypeindex
ns_register_proc POST /admin/filetype filetypeindex

ns_register_proc GET /admin/filetype/new newfiletype
ns_register_proc POST /admin/filetype/new newfiletype

ns_register_proc GET /admin/filetype/update updatefiletype
ns_register_proc POST /admin/filetype/update updatefiletype

ns_register_proc GET /admin/filetype/tag/delete delfiletypetag
ns_register_proc POST /admin/filetype/tag/delete delfiletypetag

ns_register_proc GET /admin/filetype/tag/new newfiletypetag
ns_register_proc POST /admin/filetype/tag/new newfiletypetag

ns_register_proc GET /admin/tag tagindex

ns_register_proc GET /admin/tag/delete deltag
ns_register_proc POST /admin/tag/delete deltag

ns_register_proc GET /admin/workflow workflowindex
ns_register_proc POST /admin/workflow workflowindex

ns_register_proc GET /admin/workflow/new newworkflow
ns_register_proc POST /admin/workflow/new newworkflow

ns_register_proc GET /admin/workflow/delete delworkflow
ns_register_proc POST /admin/workflow/delete delworkflow

ns_register_proc GET /admin/workflow/step/new newworkflowstep
ns_register_proc POST /admin/workflow/step/new newworkflowstep

ns_register_proc GET /admin/workflow/step/delete delworkflowstep
ns_register_proc POST /admin/workflow/step/delete delworkflowstep

ns_register_proc GET /admin/workflow/update updworkflow
ns_register_proc POST /admin/workflow/update updworkflow

ns_register_proc GET /admin/workflow/complete completeworkflow
ns_register_proc POST /admin/workflow/complete completeworkflow

ns_register_proc GET /admin/scanfolder scanfolderindex
ns_register_proc POST /admin/scanfolder scanfolderindex

ns_register_proc GET /admin/scanfolder/update updscanfolder
ns_register_proc POST /admin/scanfolder/update updscanfolder

ns_register_proc GET /acquire/scan acquire scan
ns_register_proc POST /acquire/scan acquire scan

ns_register_proc GET /acquire/upload acquire upload
ns_register_proc POST acquire/upload acquire upload

ns_register_proc GET /file/browse browsefile
ns_register_proc POST /file/browse browsefile

ns_register_proc GET /file/search searchfile
ns_register_proc POST /file/search searchfile

ns_register_proc GET /file/show showfile
ns_register_proc POST /file/show showfile

ns_register_proc GET /file/get getfile
ns_register_proc POST /file/get getfile

ns_register_proc GET /file/tag/new addfiletag
ns_register_proc POST /file/tag/new addfiletag

ns_register_proc GET /file/tag/delete delfiletag
ns_register_proc POST /file/tag/delete delfiletag

ns_register_proc GET /workflow/complete completeworkflowstep
ns_register_proc POST /workflow/complete completeworkflowstep

ns_register_proc GET /lookuptag lookuptag

ns_register_proc GET /lookupfiletype lookupfiletype

proc returnnothing { code } {

	ns_return $code text/html ""

}

proc hasrole {role} {

	if {[ns_session contains $role]} {
		return 1
	}
	return 0
}

proc checkrole {why rolename} {

	if {[ns_session contains userid]} {
		if {[ns_session contains $rolename]} {
			return filter_ok
		} else {
			ns_returnforbidden  
			return filter_return
		}
	} else {
		set returnto [ns_conn url]
		if {[string length [ns_conn query]]} {
			append returnto ?[ns_conn query]
		}
		ns_session put returnto $returnto
		ns_returnredirect [ns_conn location]/login
		return filter_ok
	}
}

proc login {} {

	if {[ns_session contains userid]} {
		seterrorstring "Already logged in."
		ns_returnredirect /
		return 
	}

	# Login requires companyname query argument.

	if {![string length [safequeryget companyname wordchar]]} {
		ns_returnredirect /
		return
	}

	ns_session put companyname [safequeryget companyname wordchar]

	set username [safequeryget username wordchar BLANK]
	set password [safequeryget password alnum]
	set classification [safequeryget classification alnum BLANK]

	if {[string match POST [ns_conn method]]} {
		# Check login, set cookie and redirect on success
		# and back here on failure
		dbi_1row -db [ns_session get companyname] {SELECT fn_checklogin(:username::varchar, :password::varchar, 
				:classification::varchar) AS ret}
		if {[string match SUCCESS [lindex $ret 0]]} {
			# Login OK, see if needs pw change
			ns_session put userid [lindex $ret 1]
			ns_session put username $username
			ns_session put classificationid [lindex $ret 3]
			set rolecount [setauth [ns_session get userid]]
			ns_session put classification $classification
			setmessagestring "Succcessful Login - $rolecount roles set."
			if {[lindex $ret 2]} {
				# Needs password change
				setmessagestring "Password has expired."
				ns_returnredirect /changepw
				return
			}
			ns_returnredirect [ns_session get returnto [ns_conn location]]
			return
		} else {
			seterrorstring "Login failed."
		}
	}

	startpage "Log In"
	startform loginform POST [ns_conn url]
	append html "<TABLE><TR><TD><span class=\"label\"> User Name: </span> \n"
	append html "</TD><TD><INPUT TYPE=TEXT NAME=username></TD></TR>\n"
	append html "<TR><TD><span class=\"label\"> Password: </span> \n"
	append html "</TD><TD><INPUT TYPE=PASSWORD NAME=password></TD></TR>\n"
	append html "<TR><TD><span class=\"label\"> Classification: </span> \n"
	append html "</TD><TD><SELECT NAME=classification>\n"

	set sql {SELECT classification 
		FROM classification 
		ORDER BY nlevel(parentpath) desc, classification} 
	set template {<OPTION> $classification </OPTION>}
	append template "\n"
	set default {<OPTION VALUE="">-- None Available --</OPTION>}
	if [catch {append html [dbi_rows -db [ns_session get companyname] $sql $template $default]}] {
		append html $default
	}

	append html "</SELECT></TD></TR>\n"
	append html "</TABLE>\n"
	append html "<INPUT TYPE=hidden NAME=companyname VALUE=[ns_session get companyname]>\n"
	endform
	endpage
	ns_return 200 text/html $html

}

proc logout {} {

	ns_session destroy
	setmessagestring "Successfully logged out."
	ns_returnredirect [ns_conn location]login

}

proc changepw {} {

	# Change the users password.  Check strength against configured setting.

	set userid [ns_session get userid]
	set pwstrength [nsv_get config pwstrength]

	if {[string match POST [ns_conn method]]} {
		
		set newpassword [safequeryget newpassword ascii]
		set confirmpassword [safequeryget confirmpassword ascii]
		
		dbi_1row -db [ns_session get companyname] {SELECT fn_checkpwstrength(
				:newpassword::varchar,
				:pwstrength::int) as goodpw}

		if {![string match $newpassword $confirmpassword]} {
			seterrorstring "Confirmation does not match."
		} elseif {!$goodpw} {
			seterrorstring "Password does not meet level $pwstrength requirements."
		} else {
			dbi_1row -db [ns_session get companyname] {SELECT fn_changepassword(
					:userid::int,
					:newpassword::varchar) as result}
			if {$result} {
				setmessagestring "Password successfully changed"
				ns_returnredirect [ns_session get returnto [ns_conn location]]
				return 
			} 
		}
		seterrorstring "Password change failed."

	}

	startpage "Change Password"
	startform changePass

	append html "<TABLE><TR><TD><span class=\"label\">New Password:</span></TD><TD>\n"
	append html "<INPUT TYPE=password NAME=newpassword></TD></TR>\n"
	append html "<TR><TD><span class=\"label\">New Password:</span></TD><TD>\n"
	append html "<INPUT TYPE=password NAME=confirmpassword></TD></TR>\n"
	append html "</TABLE>\n"

	endform
	endpage 
	
	ns_return 200 text/html $html

}

proc index {} {

	# This is the one place it is OK to return public path

	set userid [ns_session get userid]

	set html {}
	startpage "Main Page"
	if {![ns_session contains userid]} {
		append html "<TABLE><TR><TD><span class=\"label\">Log in:</span></TD><TD>\n"
		set sql {SELECT companyname FROM company}
		set template {<a href=/login?companyname=$companyname>$companyname</a><br>}
		append template "\n"
		set default {No companies found}
		if [catch {append html [dbi_rows -db dbadminpg $sql $template $default]}] {
			seterrorstring "Invalid company name"
			ns_session destroy
			ns_returnredirect /
			return
		}
		append html "</TD></TR></TABLE>\n"
	} else {
		append html "<TABLE><TR><TD><span class=\"label\">Workflow Items:</span></TD>\n"
		append html "<TD>"
		set sql {SELECT * 
			FROM fn_workflowpendings(:userid::int,
				NULL::int)}
		set template {<a href=/workflow/complete?fileid=$fileid&workflowstepid=$workflowstepid>}
		append template {$workflowtype</a> - }
		append template {<a href=/file/show?fileid=$fileid>}
		append template {$filename</a> <br>}
		set default {No workflow items pending}

		append html [dbi_rows -db [ns_session get companyname] $sql $template $default]
		
		append html "</TD></TR></TABLE>\n"
	}
	
	endpage
	ns_return 200 text/html $html
}

proc adminindex {} {

	set html {}
	startpage "Admin Page"
	append html "<TABLE>\n"
	append html "<TR><TD><span class=\"label\">System Tables </span> \n"
	append html "</TD><TD><a href=/admin/user>Users</a><BR>\n"
	append html "<a href=/admin/filetype>File Types</a><BR>\n"
	append html "<a href=/admin/tag>Tags</a><BR>\n"
	append html "<a href=/admin/workflow>Workflows</a><BR>\n"
	append html "<a href=/admin/scanfolder>Scan Folders</a>\n"
	append html "</TD></TR>\n"
	append html "</TABLE>"
	endpage
	ns_return 200 text/html $html
}

proc userindex {} {

	set html {}

	set userid [ns_session get userid]
	startpage "User List"
	append html "<TABLE><TR><TD><span class=\"label\">Users"
	append html "<a href=/admin/user/new>(New)</a></span></TD><TD>\n"

	set sql {SELECT userid, username 
		 FROM fn_users(:userid::integer, 
				NULL::integer)} 
	set template {<a href=/admin/user/update?userid=$userid>$username</a><br>}
	append template "\n"
	set default {No users exist.}
	append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

	append html "</TD></TR></TABLE>\n"

	endpage
	ns_return 200 text/html $html
}

proc newuser {} {

	if {[string match POST [ns_conn method]]} {

		set adminuserid [ns_session get userid]
		set varlist [list username alnum password ascii confirmpassword ascii \
			classificationid integer]
		foreach {var vartype} $varlist {
			set $var [safequeryget $var $vartype]
		}
		if [catch {dbi_1row -db [ns_session get companyname] {SELECT fn_user(:adminuserid::int,
				NULL::int,
				:username::varchar,
				:password::varchar,
				false::boolean,
				:classificationid::int) AS userid}} errMsg] {
			logerror Debug "$errMsg"
			seterrorstring "Could not create user"

		} else {
			ns_returnredirect /admin/user/update?userid=$userid
			return
		} 
	} 

	set html {}
	startpage "New User"
	startform newUserForm

	append html "<TABLE><TR><TD><span class=\"label\"> User Name: </span> \n"
	append html "</TD><TD><INPUT TYPE=TEXT NAME=username></TD></TR>\n"
	append html "<TR><TD><span class=\"label\"> Password: </span> \n"
	append html "</TD><TD><INPUT TYPE=PASSWORD NAME=password></TD></TR>\n"
	append html "<TR><TD><span class=\"label\"> Confirm Password: </span> \n"
	append html "</TD><TD><INPUT TYPE=PASSWORD NAME=confirmpassword></TD></TR>\n"
	append html "<TR><TD><span class=\"label\"> Max Classification: </span> \n"
	append html "</TD><TD><SELECT NAME=classificationid>\n"

	set sql {SELECT classificationid, classification as classname
		 FROM classification 
		 ORDER BY nlevel(parentpath) desc, classification}
	set template {<OPTION VALUE=$classificationid>$classname</OPTION>}
	append template "\n"
	set default {<OPTION VALUE="">-- None Available --</OPTION>}
	append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

	append html "</TABLE>\n"

	endform
	endpage
	ns_return 200 text/html $html
}

proc updateuser {} {

	set varlist [list userid int password ascii confirmpassword ascii \
		classificationid integer enabled ascii]
	foreach {var vartype} $varlist {
		set $var [safequeryget $var $vartype]
	}

	set adminuserid [ns_session get userid]

	if {[string match POST [ns_conn method]]} {

		set sql {SELECT fn_user(:adminuserid::int,
				:userid::int,
				NULL::varchar, }
		if {[string length $password] && [string length $confirmpassword]
		    && $password == $confirmpassword} {
			append sql {		:password::varchar, }
		} else {
			seterrorstring "Password not updated"
			append sql {		NULL::varchar, }
		}
		append sql {:enabled::boolean,
			    :classificationid::int) AS userid}

		dbi_1row -db [ns_session get companyname] $sql
		
		if {$userid > 0} {
			ns_returnredirect /admin/user/update?userid=$userid
		} else {
			seterrorstring "Error updating user"
		}  
	} 

	set html {}
	startpage "Edit User"
	startform editUserForm
	set adminuserid [ns_session get userid]

	dbi_1row -db [ns_session get companyname] {SELECT username, classification, enabled 
		 FROM fn_users(:adminuserid::int,
				:userid::int)}
	append html "<TABLE><TR><TD><span class=\"label\"> User Name: </span> \n"
	append html "</TD><TD>$username</TD></TR>\n"
	append html "<TR><TD><span class=\"label\"> Password: </span> \n"
	append html "</TD><TD><INPUT TYPE=PASSWORD NAME=password></TD></TR>\n"
	append html "<TR><TD><span class=\"label\"> Confirm Password: </span> \n"
	append html "</TD><TD><INPUT TYPE=PASSWORD NAME=confirmpassword></TD></TR>\n"
	append html "<TR><TD><span class=\"label\"> Max Classification: </span> \n"
	append html "</TD><TD><SELECT NAME=classificationid>\n"

	set sql {SELECT classificationid, classification as classno
		 FROM classification 
		 ORDER BY nlevel(parentpath) desc, classification}
	set template {<OPTION VALUE=$classificationid>$classno</OPTION>}
	append template "\n"
	set default {<OPTION VALUE="">-- None Available --</OPTION>}
	append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

	append html "<TR><TD><span class=\"label\"> Enabled: </TD>\n"
	append html "<TD><SELECT NAME=enabled>\n"
	append html "<OPTION VALUE=\"t\""
	if {[string match "t" $enabled]} {
		append html " SELECTED"
	}
	append html ">Yes</OPTION>\n"
	append html "<OPTION VALUE=\"f\""
	if {[string match "f" $enabled]} {
		append html " SELECTED"
	}
	append html ">No</OPTION>\n"
	append html "</SELECT></TD></TR>\n"
	
	append html "<TR><TD><span class=\"label\"> Roles: "
	append html "<a href=/admin/user/role/new?userid=$userid>(Add)</a></span></TD><TD>\n"

	set edituserid [safequeryget userid integer]

	set sql {SELECT userroleid, rolename, howgranted 
		 FROM fn_userroles(:edituserid::int, 
		 			NULL::int)}
	set luserrole [dbi_rows -db [ns_session get companyname] $sql]

	foreach {userroleid rolename howgranted} $luserrole {
		if {[string match "EXPLICIT" $howgranted]} {
			append html "<a href=/admin/user/role/delete?userroleid=$userroleid>"
			append html "$rolename</a>\n"
		} else {
			append html "$rolename\n"
		}
	}
	append html "</TABLE>\n"
	append html "<INPUT TYPE=hidden NAME=userid VALUE=$userid>\n"

	endform
	endpage
	ns_return 200 text/html $html
}

proc adduserrole {} {

	# Add role to a user.
	
	set html {}
	set adminuserid [ns_session get userid]
	set arglist [list userid integer roleid integer]
	foreach {arg argtype} $arglist {
		set $arg [safequeryget $arg $argtype]
	}

	if {[string match "POST" [ns_conn method]]} {

		dbi_1row -db [ns_session get companyname] {SELECT fn_adduserrole(:adminuserid::int, 
						:userid::int,
						:roleid::int) as userroleid}
		if {$userroleid} {
			ns_returnredirect /admin/user/update?userid=$userid
			return
		} else {
			seterrorstring "Role not added"
		}
	}
	
	startpage "Add Role to User"
	startform formAddRole

	append html "<TABLE><TR><TD><span class=\"label\"> Role: </span></TD> \n"
	append html "<TD><SELECT NAME=roleid>\n"

	set sql {SELECT * FROM role 
		 WHERE roleid NOT IN 
		 (SELECT roleid 
		  FROM fn_userroles(:userid::integer,
					NULL::integer))}
	set template {<OPTION VALUE=$roleid>$rolename</OPTION>}
	append template "\n"
	set default {<OPTION VALUE="">-- None Available --</OPTION>}
	append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

	append html "</SELECT></TD></TR></TABLE>\n"
	append html "<INPUT TYPE=HIDDEN NAME=userid VALUE=$userid>\n"

	endform
	endpage
	ns_return 200 text/html $html

}

proc deluserrole {} {

	# Show form for confirmation of delete of userrole
	# Delete it on post

	set html {}
	
	set userroleid [safequeryget userroleid integer]

	# Get file tags

	dbi_1row -db [ns_session get companyname]\
		{SELECT userid, username, rolename 
		 FROM fn_userroles(NULL::int,
			:userroleid::int)}

	if {[string match GET [ns_conn method]]} {

		startpage "Delete Role?"
		startform formConfirm

		append html "<TABLE><TR>\n"
		append html "<TD><span class=\"label\">Delete role \"$rolename\" from user \"${username}?\"</span></TD>\n"
		append html "</TR></TABLE>\n"
		append html "<input type=HIDDEN name=userroleid value=\"$userroleid\">"
		endform
		endpage

		ns_return 200 text/html $html

	} elseif {[string match POST [ns_conn method]]} {

		set adminuserid [ns_session get userid]
		dbi_1row -db [ns_session get companyname] {SELECT fn_deluserrole(:adminuserid::int, 
						:userroleid::int)}
		ns_returnredirect /admin/user/update?userid=$userid
	}
}

proc filetypeindex {} {

	set html {}

	set userid [ns_session get userid]

	startpage "File Types"
	append html "<TABLE><TR><TD><span class=\"label\">File Types"
	append html "<a href=/admin/filetype/new>(New)</a></span></TD><TD>\n"

	set sql {SELECT filetypeid, filetypename 
		FROM fn_filetypes(:userid::integer,
				  null::integer,
				  null::integer)}
	set template {<a href=/admin/filetype/update?filetypeid=$filetypeid>$filetypename</a><br>}
	append template "\n"
	set default {No file types exist yet.}
	append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

	append html "</TD></TR></TABLE>\n"

	endpage
	ns_return 200 text/html $html
}

proc newfiletype {} {

	set html {}
	set userid [ns_session get userid]
	
	set varlist {filetypename wordchar filetypedesc ascii tag1 wordchar tag2 wordchar tag3 wordchar tag4 wordchar tag5 wordchar classificationid integer retentionid integer scanoptionid integer ocr ascii }
	foreach {var vartype} $varlist {
		set $var [safequeryget $var $vartype]
	}

	if {[string match POST [ns_conn method]]} {

		# Post 

		set sql {SELECT fn_filetype (
				:userid::int4, 
				null::int4, 
				:filetypename::varchar, 
				:filetypedesc::varchar, 
				:classificationid::int4, 
				:retentionid::int4, 
				:scanoptionid::int4, 
				:ocr::bool) as result}

		if [catch {dbi_1row -db [ns_session get companyname] $sql} errMsg] {
			logerror Notice $errMsg
			seterrorstring "Unable to create new file type"
		} elseif {!$result} {
			seterrorstring "Unable to create new file type"
		} else {
			setmessagestring "Created file type $filetypename"
			set filetypeid $result

			# If tags sent, create filetypetags

			for {set i 1} {$i < 6} {incr i} {
logerror Debug "tag $i is [set tag$i]"
				if {[string length [set tag$i]]} {
					set tagname [set tag$i]
logerror Debug "creating filetag record for $tagname"
					dbi_1row -db [ns_session get companyname] {SELECT fn_addfiletypetag(
					:userid::int, 
					:filetypeid::int, 
					:tagname::varchar) as result}
				}
			}

			# redirect to index
			ns_returnredirect /admin/filetype
			return

		}
	}

	startpage "New File Type" "jsscannew"
	startform newscanform"
	append html "<TABLE><TR><TD><span class=\"label\"> File Type: </span></TD> \n"

	# Filetype input 

	append html "<TD><INPUT TYPE=text onkeyup=\"ajaxGetFileType(this.value)\" "
	append html "onblur=\"ajaxSetFileType()\" name=filetypename"
	append html " value=\"$filetypename\""
	append html ">\n"

	append html "</TD><TD>\n"
	append html "<span id=filetypenametemp></span></TD></TR>\n"

	append html "<TR><TD><span class=\"label\"> File Type Description: </span></TD> \n"
	append html "<TD><INPUT TYPE=text NAME=filetypedesc VALUE=\"$filetypedesc\"></TD></TR>\n"

	# Select list for tags. 

	for {set i 1} {$i < 6} {incr i} {
		if {$i > 1 && (![info exists tag$i] || ![string length [set tag$i]])} {
			append html "<TR id=row$i class=\"invisible\">"
		} else {
			append html "<TR id=row$i class=\"visible\">"
		}
		append html "<TD><span class=\"label\"> Tag${i}:</span></TD>  \n"
		append html "<TD><INPUT TYPE=text onkeyup=\"ajaxGetTagName${i}(this.value)\" "
		append html "onblur=\"ajaxSetTagName${i}()\" name=tag$i"
		append html " value=\"[set tag$i]\""
		append html "></TD>\n"
		append html "<TD><span id=tagtemp$i></span>\n"
		append html "</TD></TR></div>\n"
	}

	# Classification

	set sql {SELECT classificationid, classification 
		 FROM classification 
		 ORDER BY classification}
	set lclass [dbi_rows -db [ns_session get companyname] $sql]

	append html "<TD><span class=\"label\"> Classification:</span></TD><TD>\n"
	selectbox classificationid lclass classificationid classification
	append html "</TD></TR>\n"

	# Retention

	set sql {SELECT retentionid, retentionname 
		 FROM retention 
		 ORDER BY retentionname}
	set lretention [dbi_rows -db [ns_session get companyname] $sql]

	append html "<TD><span class=\"label\"> Retention:</span></TD><TD>\n"
	selectbox retentionid lretention retentionid retentionname
	append html "</TD></TR>\n"
	
	# Scanoption

	set sql {SELECT scanoptionid, scanoptionname 
		 FROM scanoption 
		 ORDER BY scanoptionname}
	set lscanoption [dbi_rows -db [ns_session get companyname] $sql]

	append html "<TD><span class=\"label\"> Scanner Options:</span></TD><TD>\n"
	selectbox scanoptionid lscanoption scanoptionid scanoptionname
	append html "</TD></TR>\n"

	# ocr

	append html "<TR><TD><span class=\"label\"> OCR:</span></TD>\n"
	append html "<TD><SELECT NAME=ocr>\n"
	append html "<OPTION VALUE=\"t\">Yes</OPTION>\n"
	append html "<OPTION VALUE=\"f\">No</OPTION>\n"
	append html "</SELECT></TD></TR>\n"
	append html "</TABLE>\n"
	endform
	endpage
	ns_return 200 text/html $html
}

proc updatefiletype {} {

	set html {}
	set userid [ns_session get userid]
	set filetypeid [safequeryget filetypeid integer]
	

	if {[string match POST [ns_conn method]] &&
	    [string length $filetypeid]} {

		# Post 
		set varlist {filetypeid integer filetypename wordchar filetypedesc ascii tag1 wordchar tag2 wordchar tag3 wordchar tag4 wordchar tag5 wordchar classificationid integer retentionid integer scanoptionid integer ocr ascii }
		foreach {var vartype} $varlist {
			set $var [safequeryget $var $vartype]
		}

		set sql {SELECT fn_filetype (
				:userid::int4, 
				:filetypeid::int4, 
				:filetypename::varchar, 
				:filetypedesc::varchar, 
				:classificationid::int4, 
				:retentionid::int4, 
				:scanoptionid::int4, 
				:ocr::bool) as result}

		if [catch {dbi_1row -db [ns_session get companyname] $sql} errMsg] {
			logerror Debug $errMsg
			seterrorstring "Unable to update file type"
		} elseif {!$result} {
			logerror Debug "$userid tried to update $filetypeid"
			seterrorstring "Unable to update new file type"
		} else {
			setmessagestring "Updated file type $filetypename"
			ns_returnredirect /admin/filetype
			return
			# redirect to index
		}

	}

	# GET

	dbi_1row -db [ns_session get companyname] {SELECT filetypeid,
			filetypename,
			filetypedesc,
			classificationid,
			retentionid,
			scanoptionid,
			ocr
			 FROM fn_filetypes(:userid::integer,
				     :filetypeid::integer,
				     null::integer)}

	startpage "Update File Type" "jsscannew"
	startform newscanform"
	append html "<TABLE><TR><TD><span class=\"label\"> File Type: </span></TD> \n"

	# Filetype input 

	append html "<TD><INPUT TYPE=text onkeyup=\"ajaxGetFileType(this.value)\" "
	append html "onblur=\"ajaxSetFileType()\" name=filetypename"
	append html " value=\"$filetypename\""
	append html ">\n"

	append html "</TD><TD>\n"
	append html "<span id=filetypenametemp></span></TD></TR>\n"

	append html "<TR><TD><span class=\"label\"> File Type Description: </span></TD> \n"
	append html "<TD><INPUT TYPE=text NAME=filetypedesc VALUE=\"$filetypedesc\"></TD></TR>\n"

	# Classification

	set sql {SELECT classificationid, classification 
		 FROM classification 
		 ORDER BY nlevel(parentpath), classification}
	set lclass [dbi_rows -db [ns_session get companyname] $sql]

	append html "<TD><span class=\"label\"> Classification:</span></TD><TD>\n"
	selectbox classificationid lclass classificationid classification
	append html "</TD></TR>\n"

	# Retention

	set sql {SELECT retentionid, retentionname 
		 FROM retention 
		 ORDER BY retentionname}
	set lretention [dbi_rows -db [ns_session get companyname] $sql]

	append html "<TD><span class=\"label\"> Retention:</span></TD><TD>\n"
	selectbox retentionid lretention retentionid retentionname
	append html "</TD></TR>\n"
	
	# Scanoption

	set sql {SELECT scanoptionid, scanoptionname 
		 FROM scanoption 
		 ORDER BY scanoptionname}
	set lscanoption [dbi_rows -db [ns_session get companyname] $sql]

	append html "<TD><span class=\"label\"> Scanner Options:</span></TD><TD>\n"
	selectbox scanoptionid lscanoption scanoptionid scanoptionname
	append html "</TD></TR>\n"

	# ocr

	lappend locr t Yes f No
	append html "<TR><TD><span class=\"label\"> OCR:</span></TD><TD>\n"
	selectbox ocr locr ocr ocrname 
	append html "</SELECT></TD></TR>\n"

	# tags

	set sql {SELECT filetypetagid, tagname 
		 FROM fn_filetypetags(:userid::integer,
		 			:filetypeid::integer,
					null::integer)}
	set template {<A HREF=/admin/filetype/tag/delete?filetypetagid=$filetypetagid>}
	append template {$tagname</A> }
	set default {No file type tags exist.}
	append html "<TR><TD><span class=\"label\">Tags"
	append html "<A HREF=/admin/filetype/tag/new?filetypeid=$filetypeid>(New)</A>:</span></TD>\n<TD>"
	append html [dbi_rows -db [ns_session get companyname] $sql $template $default]
	append html "</TD></TR>\n"
	append html "</TABLE>\n"

	append html "<INPUT TYPE=hidden NAME=filetypeid VALUE=$filetypeid>\n"
	endform
	endpage
	ns_return 200 text/html $html
}

proc tagindex {} {

	set html {}

	set userid [ns_session get userid]

	startpage "Tags"
	append html "<TABLE><TR><TD><span class=\"label\">Tags</span></TD><TD>\n"

	set sql {SELECT tagid, tagname 
		FROM tag
		WHERE NOT workflowtag
		ORDER BY tagname}
	set template {<a href=/admin/tag/delete?tagid=$tagid>$tagname</a><br>}
	append template "\n"
	set default {No tags exist yet.}
	append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

	append html "</TD></TR></TABLE>\n"

	endpage
	ns_return 200 text/html $html
}

proc deltag {} {

	# Show form for confirmation of delete of tag
	# Delete it on post

	set html {}
	
	set tagid [safequeryget tagid integer]
	set mergetagid [safequeryget mergetagid integer]
	set adminuserid [ns_session get userid]

	# Get file type tags

	dbi_1row -db [ns_session get companyname] {SELECT tagname 
		  FROM tag 
		  WHERE tagid = :tagid::int}

	if {[string match GET [ns_conn method]]} {

		startpage "Delete Tag?"
		startform formConfirm

		append html "<TABLE><TR>\n"
		append html "<TD><span class=\"label\">Delete tag \"$tagname\"?</span>"
		append html "</TD></TR>\n"
		append html "<TR><TD><span class=\"label\">Merge with tag</span></TD>\n"
		append html "<TD><SELECT NAME=mergetagid>\n"
		append html "<OPTION VALUE=\"\">-- None --</OPTION>\n"

		set sql {SELECT tagid, tagname FROM tag ORDER BY tagname}
		set template {<OPTION VALUE=$tagid>$tagname</OPTION}
		append template "\n"
		set default {<OPTION VALUE="">-- None available --</OPTION}
		append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

		append html "</SELECT></TD></TR></TABLE>\n"
		append html "<input type=HIDDEN name=tagid value=\"$tagid\">"
		endform
		endpage

		ns_return 200 text/html $html

	} elseif {[string match POST [ns_conn method]]} {

		if {[string length $mergetagid]} {
			dbi_1row -db [ns_session get companyname] {SELECT fn_deltag(:adminuserid::int, 
					:tagid::int, 
					:mergetagid::int) AS result}
		} else {
			dbi_1row -db [ns_session get companyname] {SELECT fn_deltag(:adminuserid::int, 
					:tagid::int, 
					NULL::int) AS result}
		}

		if {$result} {
			setmessagestring "Deleted tag $tagname"
		} else {
			seterrorstring "Unable to delete tag."
		}

		ns_returnredirect /admin/tag
	}
}

proc workflowindex {} {

	# List of existing workflows for admin use with links to update or delete

	set html {}

	set userid [ns_session get userid]

	startpage "Workflows"
	append html "<TABLE><TR><TD><span class=\"label\">Workflows "
	append html "<a href=/admin/workflow/new>(New)</a></span></TD><TD>\n"

	set sql {SELECT workflowid, workflowname 
		FROM workflow
		ORDER BY workflowname}
	set template {<a href=/admin/workflow/update?workflowid=$workflowid>$workflowname</a> }
	append template {<a href=/admin/workflow/delete?workflowid=$workflowid>(Delete)</a><br>}
	append template "\n"
	set default {No workflowss exist yet.}
	append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

	append html "</TD></TR></TABLE>\n"

	endpage
	ns_return 200 text/html $html
}

proc newworkflow {} {

	# Input and processing of creation of a new workflow

	set adminuserid [ns_session get userid]

	if {[string match POST [ns_conn method]]} {

		set varlist [list workflowname wordchar filetypeid integer tagid integer]
		foreach {var vartype} $varlist {
			set $var [safequeryget $var $vartype]
		}
		if {![string length $workflowname]} {
			seterrorstring "Workflow name cannot be empty."
		} else {

			dbi_1row -db [ns_session get companyname] {SELECT fn_workflow(
					NULL::int,
					:adminuserid::int,
					:workflowname::varchar,
					:filetypeid::int,
					:tagid::int)
					AS workflowid}

			if {$workflowid > 0} {
				ns_returnredirect /admin/workflow/update?workflowid=$workflowid
			} else {
				seterrorstring "Problem creating workflow"
			}
		}
	} 

	set html {}
	startpage "New Workflow"
	startform newWorkflowForm

	append html "<TABLE><TR><TD><span class=\"label\"> Workflow Name: </span> \n"
	append html "</TD><TD><INPUT TYPE=TEXT NAME=workflowname></TD></TR>\n"

	append html "<TR><TD><span class=\"label\"> File Type: </span> \n"
	append html "</TD><TD>"

	set sql {SELECT DISTINCT filetypeid , filetypename
		FROM fn_filetypes (:adminuserid::int4, null::int4, null::int4)
		ORDER BY filetypename}
	set filetypelist [dbi_rows -db [ns_session get companyname] $sql]
	selectbox filetypeid filetypelist filetypeid filetypename 

	append html "</TD></TR>\n"

	append html "<TR><TD><span class=\"label\"> Tag: </span> \n"
	append html "</TD><TD>\n"

	set sql {SELECT tagid, tagname
		 FROM tag
		 WHERE NOT workflowtag
	 	 ORDER BY tagname}
	set taglist [dbi_rows -db [ns_session get companyname] $sql]
	selectbox tagid taglist tagid tagname 

	append html "</TD></TR>\n"
	append html "</TABLE>\n"

	endform
	endpage
	ns_return 200 text/html $html
}

proc updworkflow {} {

	# Input and processing for update of a workflow including adding steps

	set adminuserid [ns_session get userid]
	set workflowid [safequeryget workflowid integer]

	set sql {SELECT * FROM fn_workflows(
		:adminuserid::int,
		:workflowid::int)}
	dbi_1row -db [ns_session get companyname] $sql

	if {[string match POST [ns_conn method]]} {

		set varlist [list workflowname wordchar filetypeid integer tagid integer]
		foreach {var vartype} $varlist {
			set $var [safequeryget $var $vartype]
		}
		dbi_1row -db [ns_session get companyname] {SELECT fn_workflow(
				:workflowid::int,
				:adminuserid::int,
				:workflowname::varchar,
				:filetypeid::int,
				:tagid::int)
				AS workflowid}

		if {$workflowid > 0} {
			ns_returnredirect /admin/workflow/update?workflowid=$workflowid
		}
	} 

	set html {}
	startpage "Update Workflow"
	startform updWorkflowForm

	append html "<TABLE><TR><TD><span class=\"label\"> Workflow Name: </span> \n"
	append html "</TD><TD><INPUT TYPE=TEXT NAME=workflowname VALUE=\"$workflowname\"></TD></TR>\n"

	append html "<TR><TD><span class=\"label\"> File Type: </span> \n"
	append html "</TD><TD>"

	set sql {SELECT DISTINCT filetypeid, filetypename
		FROM fn_filetypes (:adminuserid::int4, null::int4, null::int4)
		ORDER BY filetypename}
	set lfiletype [dbi_rows -db [ns_session get companyname] $sql]

	selectbox filetypeid lfiletype filetypeid filetypename
	append html "</TD></TR>\n"

	append html "<TR><TD><span class=\"label\"> Tag: </span> \n"
	append html "</TD><TD>\n"

	set sql {SELECT tagid, tagname
		 FROM tag
		 WHERE NOT workflowtag
	 	 ORDER BY tagname}
	set ltag [dbi_rows -db [ns_session get companyname] $sql]

	selectbox tagid ltag tagid tagname 
	append html "</TD></TR>\n"
	append html "</TABLE>\n"
	append html "<INPUT TYPE=hidden NAME=workflowid VALUE=$workflowid>"
	endform

	# Workflow steps

	append html "<TABLE>"

	append html "<TR><TD><span class=\"label\"> Workflow Steps: </span> \n"
	append html "</TD><TD>\n"
	append html "<A HREF=/admin/workflow/step/new?workflowid=$workflowid>(New)</A><BR>\n"

	set sql {SELECT repeat('- ',nlevel(parentpath)) as level, * FROM fn_workflowsteps(
			:adminuserid::int,
			:workflowid::int,
			NULL::int)}
	set template {<A HREF=/admin/workflow/step/delete?workflowstepid=$workflowstepid>}
	append template { $level $workflowtype $wfsusername </A>}
	append template {<A HREF=/admin/workflow/step/new?workflowid=$workflowid&parentworkflowstepid=$workflowstepid>(New)</A><BR>}
	append template "\n"
	set default {No steps exist yet}
	append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

	append html "</TD></TR>\n"

	append html "</TABLE>"

	endpage
	ns_return 200 text/html $html


}

proc delworkflow {} {
	
	# Confirmation and processing of deletion of a workflow.  This should
	# not delete the associated tags since they will remain linked to history.
}

proc completeworkflow {} {

	# Confiration and processing of early termination of workflow.  Only workflowadmin
	# can do it, actually tags file.
}

proc newworkflowstep {} {

	# Create a new workflow step with the given parent step (or at the root if none)

	set adminuserid [ns_session get userid]
	set workflowid [safequeryget workflowid integer]
	set parentworkflowstepid [safequeryget parentworkflowstepid integer 0]

	if {[string match POST [ns_conn method]]} {

		# do the insert

		set userid [safequeryget userid integer]
		set tagid [safequeryget tagid integer]
		set revertafter [safequeryget revertafter ascii]

		if {![string length $userid] || ![string length $tagid]} {
			seterrorstring "User and type are both required."
			ns_returnredirect /admin/workflow/update?workflowid=$workflowid
			return
		} 

		if {![string length parentworkflowstepid]} {
			set sql {SELECT fn_addworkflowstep(
					:userid::int, 
					:workflowid::int, 
					NULL::int, 
					:userid::int, 
					:tagid::int
					:revertafter::interval) AS workflowstepid}
		} else {
			set sql {SELECT fn_addworkflowstep(
					:adminuserid::int, 
					:workflowid::int, 
					:parentworkflowstepid::int, 
					:userid::int, 
					:tagid::int,
					:revertafter::interval) AS workflowstepid}
		}
		dbi_1row -db [ns_session get companyname] $sql
		if {$workflowstepid == 0} {
			seterrorstring "Unable to create workflowstep"
		}
		ns_returnredirect /admin/workflow/update?workflowid=$workflowid
		return
	}

	startpage "New Workflow Step"
	startform newWorkflowstep
	append html "<TABLE>\n"
	append html "<TR><TD><span class=\"label\"> User:</span></TD>  \n"
	append html "<TD>"

	set sql {SELECT userid, username 
		 FROM fn_users(:adminuserid::int,
				NULL::int)
		 ORDER BY username}
	set lusers [dbi_rows -db [ns_session get companyname] $sql]

	selectbox userid lusers userid username
	append html "</TD></TR>"

	append html "<TR><TD><span class=\"label\"> Workflow Type:</span></TD>  \n<TD>"

	set sql {SELECT tagid, tagname
		 FROM tag
		 WHERE workflowtag
	 	 ORDER BY tagname}

	set ltags [dbi_rows -db [ns_session get companyname] $sql]
	selectbox tagid ltags tagid tagname
	append html "</TD></TR>\n"

	append html "<TR><TD><span class=\"label\"> Revert After:</span></TD>  \n<TD>"
	append html "<INPUT TYPE=text NAME=revertafter VALUE=\"2 weeks\">"
	append html "</TD></TR>\n"

	append html "</TABLE>"
	append html "<INPUT TYPE=hidden NAME=workflowid VALUE=$workflowid>\n"
	append html "<INPUT TYPE=hidden NAME=parentworkflowstepid VALUE=$parentworkflowstepid>\n"
	endform
	ns_return 200 text/html $html

}

proc delworkflowstep {} {

	# Confirmation and processing of deletion of a workflow step.
	# we dont bother to update them.  Just delete and create.

	set userid [ns_session get userid]

	set html {}
	
	set workflowstepid [safequeryget workflowstepid integer]

	dbi_1row -db [ns_session get companyname] {SELECT wfsusername, workflowtype 
		  FROM fn_workflowsteps(:userid::int, 
		  NULL::int,
		  :workflowstepid::int)}

	if {[string match GET [ns_conn method]]} {

		startpage "Delete Workflow Step?"
		startform formConfirm

		append html "<TABLE><TR>\n"
		append html "<TD><span class=\"label\">Delete step \"$workflowtype\""
		append html " for user \"${wfsusername}?\"</span></TD>\n"
		append html "</TR></TABLE>\n"
		append html "<input type=HIDDEN name=workflowstepid value=\"$workflowstepid\">"
		endform
		endpage

		ns_return 200 text/html $html

	} elseif {[string match POST [ns_conn method]]} {

		dbi_1row -db [ns_session get companyname] {SELECT fn_delworkflowstep(:userid::int, 
				:workflowstepid::int) as workflowid}
		ns_returnredirect /admin/workflow/update?workflowid=$workflowid
	}
}

proc newfiletypetag {} {

	# Show form for adding a tag to a filetype and add it on post

	set html {}
	
	set filetypeid [safequeryget filetypeid integer]
	set tagname [safequeryget tag1 wordchar]
	set adminuserid [ns_session get userid]

	if {[string match GET [ns_conn method]]} {

		startpage "Add Tag to File Type" "jsscannew"
		startform newscanform

		append html "<TABLE><TR>\n"
		append html "<TD><span class=\"label\"> Tag:</span></TD>  \n"
		append html "<TD><INPUT TYPE=text onkeyup=\"ajaxGetTagName1(this.value)\" "
		append html "onblur=\"ajaxSetTagName1()\" name=tag1"
		append html "></TD>\n"
		append html "<TD><span id=tagtemp1></span>\n"
		append html "</TD></TR></div>\n"
		append html "</TR></TABLE>\n"
		append html "<input type=HIDDEN name=filetypeid value=\"$filetypeid\">"

		endform
		endpage

		ns_return 200 text/html $html

	} elseif {[string match POST [ns_conn method]]} {

		if [catch {dbi_1row -db [ns_session get companyname] {SELECT fn_addfiletypetag(:adminuserid::int, 
			:filetypeid::int, 
			:tagname::varchar)}} errMsg] {
			seterrorstring "Error adding file type tag."
		}
		ns_returnredirect /admin/filetype/update?filetypeid=$filetypeid
		
	}
}

proc delfiletypetag {} {

	# Show form for confirmation of delete of filetypetag
	# Delete it on post

	set html {}
	
	set filetypetagid [safequeryget filetypetagid integer]
	set adminuserid [ns_session get userid]

	# Get file type tags

	dbi_1row -db [ns_session get companyname] {SELECT filetypeid, filetypename, tagname 
		  FROM fn_filetypetags(:adminuserid::int, 
		  NULL::int, 
		  :filetypetagid::int)}

	if {[string match GET [ns_conn method]]} {

		startpage "Delete Tag?"
		startform formConfirm

		append html "<TABLE><TR>\n"
		append html "<TD><span class=\"label\">Delete tag \"$tagname\""
		append html " from filetype \"${filetypename}?\"</span></TD>\n"
		append html "</TR></TABLE>\n"
		append html "<input type=HIDDEN name=filetypetagid value=\"$filetypetagid\">"
		endform
		endpage

		ns_return 200 text/html $html

	} elseif {[string match POST [ns_conn method]]} {

		dbi_1row -db [ns_session get companyname] {SELECT fn_delfiletypetag(:adminuserid::int, 
				NULL::int, 
				:filetypetagid::int)}
		ns_returnredirect /admin/filetype/update?filetypeid=$filetypeid
	}
}

proc acquire { docsource } {

	# Main screen for acquiring documents.  Calls acquiredoc  proc which 
	# returns an error if certain known errors occur.  It should return
	# a filename if all went well, so we could link to it.

	set html {}
	set fileexists 0
	set newfilename ""
	set date [ns_fmttime [ns_time] "%Y%m%d"]
	set companyname [ns_session get companyname]

	if {[string match [ns_conn method] POST]} {

		set varlist {filetypename wordchar tag1 wordchar tag2 wordchar tag3 wordchar tag4 wordchar tag5 wordchar brightness integer contrast integer mode alnum duplex alpha date alnum duplicate integer dupfile wordchar thefile ascii}
		foreach {var vartype} $varlist {
			set $var [safequeryget $var $vartype]
			logerror Debug "From querystring, $var is [set $var]"
		}
		if {$duplicate == ""} {set duplicate 0}
 
		# Default scanner options for the filetype.

		set varlist {brightness contrast mode duplex}
		dbi_1row -db [ns_session get companyname] {SELECT brightness as brightness_def, 
				contrast as contrast_def, 
				mode as mode_def, 
				duplex as duplex_def
			 FROM fn_scanoptions(:filetypename::varchar)}
		foreach var $varlist {
			if {![string length [set $var]]} {
				# Var was not provided, so use filetype
				# setting or default if new type.

				set $var [set ${var}_def]
				logerror Debug "After replacement, $var is [set $var]"
			}
		} 

		# Data validation
		# filetypename or filetypenamenew must be present

		if {![string length $filetypename]} {
			seterrorstring "filetype cannot be blank"
		} elseif {[catch {clock scan $date}]} {	
			seterrorstring "Not a valid date"
		} elseif {[string match upload $docsource] &&
			 ![string length $thefile]} {
			seterrorstring "File name not provided"
		} else {
		
			if {[catch {set filename [acquiredoc $docsource $filetypename $date $duplicate $dupfile]} error]} {
				seterrorstring $error
			} else {
				set fileid [writefiletodb $filename $duplicate]
				setmessagestring "<a href=/file/show?fileid=${fileid}>$filename</a>"
			}
		}
	}

	if {[string match "*File*exists*" [ns_session get errorstring]]} {
		set fileexists 1
	}
	set iserror [ns_session contains errorstring]

	# GET or result of POST

	startpage "Acquire New Document" "jsscannew"
	startform newscanform POST [ns_conn url] "multipart/form-data"
	append html "<TABLE><TR><TD><span class=\"label\"> Type: </span></TD> \n"

	# Filetype input 

	append html "<TD><INPUT TYPE=text onkeyup=\"ajaxGetFileType(this.value)\" "
	append html "onblur=\"ajaxSetFileType()\" name=filetypename"
	if {$fileexists || $iserror} {
		append html " value=\"$filetypename\""
	}
	append html ">\n"

	append html "</TD><TD>\n"
	append html "<span id=filetypenametemp></span></TD></TR>\n"

	# Date 

	append html "<TR><TD>\n"
	append html "<span class=\"label\">Date: </span> "
	append html "</TD><TD COLSPAN=2>\n"
	append html "<INPUT TYPE=text NAME=date VALUE=\"$date\"></TD></TR>\n"


	# Select list for tags.  Dont remember unless fileexists or is error

	for {set i 1} {$i < 6} {incr i} {
		if {$i > 1 && (![info exists tag$i] || ![string length [set tag$i]])} {
			append html "<TR id=row$i class=\"invisible\">"
		} else {
			append html "<TR id=row$i class=\"visible\">"
		}
		append html "<TD><span class=\"label\"> Tag${i}:</span></TD>  \n"
		append html "<TD><INPUT TYPE=text onkeyup=\"ajaxGetTagName${i}(this.value)\" "
		append html "onblur=\"ajaxSetTagName${i}()\" name=tag$i"
		if {$fileexists || $iserror} {
			append html " value=\"[set tag$i]\""
		}
		append html "></TD>\n"
		append html "<TD><span id=tagtemp$i></span>\n"
		append html "</TD></TR></div>\n"
	}

	if {[string match scan $docsource]} {

		# Optional overrides for scanner settings which are associated 
		# with the filetype by default if creating new.
		# brightness contrast mode duplex
		# TODO remember settings if duplicate!!

		append html "<TR id=rowShowOptions class=\"visible\" onclick=\"showScanOptions()\">\n"
		append html "<TD></TD><TD COLSPAN=2><a href=#>+ Options</a></TD></TR>\n"
		append html "<TR id=rowHideOptions class=\"invisible\" onclick=\"hideScanOptions()\">\n"
		append html "<TD></TD><TD COLSPAN=2><a href=#>- Options</a></TD></TR>\n"
		append html "<TR id=row6 class=\"invisible\"><TD>\n"
		append html "<span class=\"label\">Brightness:</span></TD>\n"
		append html "<TD COLSPAN=2><INPUT TYPE=TEXT NAME=brightness"
		if {$fileexists || $iserror} {
			append html " VALUE=\"$brightness\""
		}
		append html ">\n"
		append html "</TD></TR>\n"

		append html "<TR id=row7 class=\"invisible\">"
		append html "<TD><span class=\"label\">Contrast:</span></TD>\n"
		append html "<TD COLSPAN=2> <INPUT TYPE=TEXT NAME=contrast"
		if {$fileexists || $iserror} {
			append html " VALUE=\"$contrast\""
		}
		append html ">\n"
		append html "</TD></TR>\n"

		# Color Mode 

		append html "<TR id=row8 class=\"invisible\"><TD>\n"
		append html "<span class=\"label\">Mode:</span></TD>\n"
		append html "<TD COLSPAN=2><SELECT NAME=mode>\n"
		append html "<OPTION VALUE=\"\">-- Select Mode --\n"
		append html "<OPTION VALUE=Lineart"
		if {($fileexists || $iserror) && [string match $mode "Lineart"]} {
			append html " SELECTED"
		}
		append html ">Black and White\n"
		append html "<OPTION VALUE=Gray"
		if {($fileexists || $iserror) && [string match $mode "Gray"]} {
			append html " SELECTED"
		}
		append html ">Gray\n"
		append html "<OPTION VALUE=Color"
		if {($fileexists || $iserror) && [string match $mode "Color"]} {
			append html " SELECTED"
		}
		append html ">Color\n"
		append html "</SELECT></TD</TR>\n"

		# Duplex or simplex

		append html "<TR id=row9 class=\"invisible\"><TD>\n"

		append html "<span class=\"label\">Sides: </span></TD>\n"
		append html "<TD COLSPAN=2><SELECT NAME=duplex>\n"
		append html "<OPTION VALUE=\"\">-- Select Sides --\n"
		append html "<OPTION VALUE=t"
		if {($fileexists || $iserror) && ([info exists duplex] && 
			[string match "t" $duplex])} {
			append html " SELECTED"
		}
		append html ">Duplex\n"
		append html "<OPTION VALUE=f"
		if {($fileexists || $iserror) && ([info exists duplex] && 
			![string match "t" $duplex])} {
			append html " SELECTED"
		}
		append html ">Simplex\n"
		append html "</SELECT></TD</TR>\n"

	} elseif {[string match upload $docsource]} {

		append html "<TR><TD><span class=\"label\">File: </span></TD>\n"
		append html "<TD><INPUT TYPE=FILE NAME=thefile></TD></TR>\n"
	}

	append html "</TABLE>\n"

	# File exists, what are you going to do?

	if {$fileexists} {
		append html "<FONT COLOR=RED>Duplicate File Option: </FONT>"
		append html "<SELECT NAME=duplicate>\n"	
		foreach {key label} {1 Replace 2 "Make Unique" 3 Append} {
			append html "<OPTION VALUE=$key>$label</OPTION>\n"
		}
		append html "</SELECT>\n"
		
		# Provide list of existing files with same type and date
		# This might make more sense to check the database instead of
		# the filesystem.  TODO

		append html "<SELECT NAME=dupfile>\n"	
		set fullpath [file dirname [makepath ${filetypename}_${date}.pdf]]
		foreach filename [lsort [glob [file join $fullpath ${filetypename}_${date}*]]] {
			set thefile [file root [file tail $filename]]
			append html "<OPTION VALUE=$thefile >$thefile</OPTION>\n"
		}
		append html "</SELECT>\n"
		
	}

	endform
	endpage
	ns_return 200 text/html $html
}

proc searchfile {} {

	# Search input form and results

	set html {}
	set lfiles {}
	set userid [ns_session get userid]

	set varlist {keywords ascii tags alnum filetypename wordchar datefrom ascii dateto ascii tag1 wordchar tag2 wordchar tag3 wordchar tag4 wordchar tag5 wordchar}
	foreach {var vartype} $varlist {
		set $var [safequeryget $var $vartype]
	}

	startpage "Search Files" "jsscannew"

	if {[string match POST [ns_conn method]]} {
		
		# This was a post, so process inputs and fall through
		
		set tags ""
		for {set i 1} {$i < 6} {incr i} {
			if {[string length [set tag$i]]} {
				lappend tags [set tag$i]
			}
		}
		regsub -all {  *} [string trim $tags] {,} atags
		regsub -all {  *} [string trim $keywords] {,} akeywords
		regsub -all {  *} [string trim $filetypename] {,} afiletypename
logerror Debug "atags is $atags"

		set sql {SELECT fileid, filename, headline 
			 FROM fn_searchresults(
				:userid::int,
				string_to_array(:atags,','), 
				string_to_array(:akeywords,','), 
				string_to_array(:afiletypename,','),
				:datefrom::date,
				:dateto::date)}
		if [catch {set lfiles [dbi_rows -db [ns_session get companyname] $sql]} errMsg] {
			setmessagestring $errMsg
			ns_returnredirect [ns_conn url]
		}
	}

	startform "newscanform"

	append html "<TABLE>\n"

	append html "<TR><TD><span class=\"label\"> Type: </span></TD> \n"

	# Filetype input 

	append html "<TD><INPUT TYPE=text onkeyup=\"ajaxGetFileType(this.value)\" "
	append html "onblur=\"ajaxSetFileType()\" name=filetypename"
	append html " VALUE=$filetypename>\n"

	append html "</TD><TD>\n"
	append html "<span id=filetypenametemp></span></TD></TR>\n"

	append html "<TR><TD><span class=\"label\"> Keywords: </span></TD> \n"
	append html "<TD><INPUT TYPE=text NAME=keywords VALUE=\"$keywords\"></TD></TR>\n"

	append html "<TR><TD><span class=\"label\"> Date From: </span></TD> \n"
	append html "<TD><INPUT TYPE=text NAME=datefrom VALUE=\"$datefrom\"></TD></TR>\n"

	append html "<TR><TD><span class=\"label\"> Date To: </span></TD> \n"
	append html "<TD><INPUT TYPE=text NAME=dateto VALUE=\"$dateto\"></TD></TR>\n"

	# Select list for tags.

	for {set i 1} {$i < 6} {incr i} {
		if {$i > 1 && (![info exists tag$i] || ![string length [set tag$i]])} {
			append html "<TR id=row$i class=\"invisible\">"
		} else {
			append html "<TR id=row$i class=\"visible\">"
		}
		append html "<TD><span class=\"label\"> Tag${i}:</span></TD>  \n"
		append html "<TD><INPUT TYPE=text onkeyup=\"ajaxGetTagName${i}(this.value)\" "
		append html "onblur=\"ajaxSetTagName${i}()\" name=tag$i"
		append html " value=\"[set tag$i]\""
		append html "></TD>\n"
		append html "<TD><span id=tagtemp$i></span>\n"
		append html "</TD></TR></div>\n"
	}

	append html "</TABLE>\n"

	endform

	if {[string length $lfiles] > 0} {

		append html "<TABLE>\n"
		foreach {fileid filename headline} $lfiles {
			append html "<TR><TD><a href=/file/show?fileid=$fileid>"
			append html "$filename</a></TD>\n"
			append html "<TD>$headline</TD></TR>\n"
		}
		append html "</TABLE>\n"
	}
	endpage

	ns_return 200 text/html $html
}

proc uploadfile { filename } {

	# file uploader.  Gets a file from the current form input
	# and saves it with provided name

	set infile [ns_queryget "thefile.tmpfile"]

	# Check file type

	set filetype [exec file $infile]
	logerror Debug $filetype
	if {[string match "*PDF document*" $filetype]} {
		set ext "pdf"
	} elseif {[string match "*TIFF image data*" $filetype]} {
		set ext "tif"
	} else {
		seterrorstring "Unsupported file type"
		return -code error
	}

	set outfile [makepath ${filename} "" ${ext}]

	if {[catch {exec cp [ns_queryget "thefile.tmpfile"] $outfile} errMsg]} {
		seterrorstring "Error copying file: $errMsg"
		return -code error 0
	} 

	return $ext
}

proc browsefile {} {

	# File browser.  List of links based on date or filetype.
	# If get includes specific date or filetype, show files in that realm
	# Links point to file viewer.

	set html {}
	set userid [ns_session get userid]
	set classificationid [ns_session get classificationid]

	set varlist {how alpha type alnum day integer month integer year integer tag wordchar}
	foreach {var vartype} $varlist {
		set $var [safequeryget $var $vartype]
	}

	startpage "Browse Files"
	append html "<TABLE>\n"

	if {![string length $how]} {
		# Return the browse choices
		append html "<TR><TD class=label>\n"
		append html "<a href=/file/browse?how=date>Browse by Date</a></TD></TR>\n"
		append html "<TR><TD class=label>\n"
		append html "<a href=/file/browse?how=type>Browse by Type</a></TD></TR>"
		append html "<TR><TD class=label>\n"
		append html "<a href=/file/browse?how=tag>Browse by Tag</a></TD></TR>"
	} else {
		if {[string match "date" $how]} {

			# Browse by date

			if {[string length $day] && [string length $month] && 
			    [string length $year] &&
			    ![catch "clock scan [file join $month $day $year]"]} {
				append html "<TR><TD class=label>\n"
				append html "<a href=/file/browse?how=date>Browse by Date: </a>"
				append html "<a href=/file/browse?how=date&year=$year>$year</a>/"
				append html "<a href=/file/browse?how=date&year=$year&month=$month>$month</a>/"
				append html "$day </TD>"
				append html "<TD>\n"

				set sql {SELECT fileid, filename 
					FROM fn_browsefiles( 
					:userid::int, 
					'date'::varchar, 
					:year::int, 
					:month::int,
					:day::int, 
					null::varchar,
					null::varchar)} 
				set template {<a href=/file/show?fileid=$fileid>$filename</a><br>}
				append template "\n"
				set default {No files found.}
				append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

				append html "</TD></TR>\n"
			} elseif {[string length $month] && [string length $year]} {
				append html "<TR><TD class=label>\n"
				append html "<a href=/file/browse?how=date>Browse by Date: </a>"
				append html "<a href=/file/browse?how=date&year=$year>$year</a>/"
				append html "$month </TD>"
				append html "<TD>\n"

				set sql {SELECT fn_browsedates as day 
					  FROM fn_browsedates(
					  :userid::int, 
					  :year::int, 
					  :month::int)}
				set template {<a href=/file/browse?how=date&year=$year&month=$month&day=$day>$day</a><br>}
				append template "\n"
				set default {No dates found.}
				append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

				append html "</TD>\n"
				append html "<TD>\n"

				set sql {SELECT fileid, filename 
					 FROM fn_browsefiles( 
					:userid::int, 
					'date'::varchar, 
					:year::int, 
					:month::int, 
					null::int, 
					null::varchar, 
					null::varchar)}
				set template {<a href=/file/show?fileid=$fileid>$filename</a><br>}
				append template "\n"
				set default {No files found.}
				append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

				append html "</TD></TR>\n"
			} elseif {[string length $year]} {
				append html "<TR><TD class=label>\n"
				append html "<a href=/file/browse?how=date>Browse by Date: </a>"
				append html "$year </TD>"
				append html "<TD>\n"

				set sql {SELECT fn_browsedates as month 
						FROM fn_browsedates(
							:userid::int, 
							:year::int, 
							NULL::int)}
				set template {<a href=/file/browse?how=date&year=$year&month=$month>$month</a><br>}
				append template "\n"
				set default {No dates found.}
				append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

				append html "</TD>\n"
				append html "<TD>\n"

				set sql {SELECT fileid, filename FROM fn_browsefiles( 
					:userid::int, 
					'date'::varchar, 
					:year::int, 
					null::int,
					null::int, 
					null::varchar, 
					null::varchar) 
					LIMIT 30}
				set template {<a href=/file/show?fileid=$fileid>$filename</a><br>}
				append template "\n"
				set default {No files found.}
				append html [dbi_rows -db [ns_session get companyname] $sql $template $default] 

				append html "</TD></TR>"
			} else {
				append html "<TR><TD class=label>Browse by Date: </TD>"
				append html "<TD>\n"

				set sql {SELECT fn_browsedates as year 
					 FROM fn_browsedates(
					:userid::int, 
					NULL::int, 
					NULL::int)}
				set template {<a href=/file/browse?how=date&year=$year>$year</a><br>}
				append template "\n"
				set default {No dates found.}
				append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

				append html "</TD></TR>\n"
			}
		} elseif {[string match "type" $how]} {

			# Browse by type

			if {[string length $type]} {
				append html "<TR><TD class=label>"
				append html "<a href=/file/browse?how=type>\n"
				append html "Browse by type:</a> $type</TD>\n"
				append html "<TD>\n"

				set sql {SELECT fileid, filename 
					 FROM fn_browsefiles( 
					:userid::int, 
					'type'::varchar, 
					NULL::int, 
					NULL::int, 
					NULL::int, 
					:type::varchar, 
					null::varchar)}
				set template {<a href=/file/show?fileid=$fileid>$filename</a><br>}
				append template "\n"
				set default {No files found.}
				append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

				append html "</TD></TR>\n"
			} else {
				append html "<TR><TD class=label>Browse by Type: </TD>\n"
				append html "<TD>\n"

				set sql {SELECT DISTINCT filetypename, filetypeid 
					FROM fn_filetypes (:userid::int4, 
						null::int4,
						:classificationid::int4)
					ORDER BY filetypename}
				set template {<a href=/file/browse?how=type&type=$filetypename >$filetypename </a><br>}
				append template "\n"
				set default {No filetypes exist yet.}
				append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

				append html "</TD></TR>\n"
			}
		} elseif {[string match "tag" $how]} {

			# Browse by tag

			if {[string length $tag]} {
				append html "<TR><TD class=label>"
				append html "<a href=/file/browse?how=tag>\n"
				append html "Browse by tag:</a> $tag</TD>\n"
				append html "<TD>\n"

				set sql {SELECT fileid, filename 
					 FROM fn_browsefiles( 
					:userid::int, 
					'tag'::varchar, 
					NULL::int, 
					NULL::int, 
					NULL::int, 
					NULL::varchar, 
					:tag::varchar)}
				set template {<a href=/file/show?fileid=$fileid>$filename</a><br>}
				append template "\n"
				set default {No files found.}
				append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

				append html "</TD></TR>\n"
			} else {
				append html "<TR><TD class=label>Browse by Tag: </TD>\n"
				append html "<TD>\n"

				set sql {SELECT DISTINCT tagname, tagid 
					FROM fn_filetags (:userid::int4,
							NULL::int4,
							NULL::int4) 
					ORDER BY tagname }
				set template {<a href=/file/browse?how=tag&tag=$tagname>$tagname</a><BR>}
				append template "\n"
				set default {No tags exist yet.}
				append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

				append html "</TD></TR>\n"
			}
		} 
	}

	endpage

	ns_return 200 text/html $html
}

proc addfiletag {} {

	# Show form for adding a tag to a file and add it on post

	set html {}
	
	set fileid [safequeryget fileid integer]
	set tagname [safequeryget tag1 wordchar]
	set adminuserid [ns_session get userid]

	if {[string match GET [ns_conn method]]} {

		startpage "Add Tag to File" "jsscannew"
		startform newscanform

		append html "<TABLE><TR>\n"
		append html "<TD><span class=\"label\"> Tag:</span></TD>  \n"
		append html "<TD><INPUT TYPE=text onkeyup=\"ajaxGetTagName1(this.value)\" "
		append html "onblur=\"ajaxSetTagName1()\" name=tag1"
		append html "></TD>\n"
		append html "<TD><span id=tagtemp1></span>\n"
		append html "</TD></TR></div>\n"
		append html "</TR></TABLE>\n"
		append html "<input type=HIDDEN name=fileid value=\"$fileid\">"

		endform
		endpage

		ns_return 200 text/html $html

	} elseif {[string match POST [ns_conn method]]} {

		if [catch {dbi_1row -db [ns_session get companyname] {SELECT fn_addfiletag(:adminuserid::int, 
			:fileid::int, 
			:tagname::varchar)}} errMsg] {
			seterrorstring "Error adding file tag."
		}
		ns_returnredirect /file/show?fileid=$fileid
		
	}
}

proc delfiletag {} {

	# Show form for confirmation of delete of filetag
	# Delete it on post

	set html {}
	
	set filetagid [safequeryget filetagid integer]
	set adminuserid [ns_session get userid]

	# Get file tags

	dbi_1row -db [ns_session get companyname] {SELECT fileid, filename, tagname 
		  FROM fn_filetags(:adminuserid::int, 
		  NULL::int, 
		  :filetagid::int)}

	if {[string match GET [ns_conn method]]} {

		startpage "Delete Tag?"
		startform formConfirm

		append html "<TABLE><TR>\n"
		append html "<TD><span class=\"label\">Delete tag \"$tagname\" from file \"${filename}?\"</span></TD>\n"
		append html "</TR></TABLE>\n"
		append html "<input type=HIDDEN name=filetagid value=\"$filetagid\">"
		endform
		endpage

		ns_return 200 text/html $html

	} elseif {[string match POST [ns_conn method]]} {

		dbi_1row -db [ns_session get companyname] {SELECT fn_delfiletag(:adminuserid::int, 
				NULL::int, 
				:filetagid::int)}
		ns_returnredirect /file/show?fileid=$fileid
	}
}

proc showfile {} {

	# Display a scanned files attributes and optionally allow modification if the
	# role allows it.

	set html {}
	
	set fileid [safequeryget fileid integer]
	set userid [ns_session get userid]

	startpage "Show File"

	# Get file information

	dbi_1row -db [ns_session get companyname] {SELECT fileid, filename, username, filetypename, filedate, 
		  parentfileid, childfileid, scandate, ocrdata 
		  FROM fn_file(:userid::int, 
				:fileid::int)}

	# Get file tags

	set sql {SELECT filetagid, tagname 
		 FROM fn_filetags(:userid::int, 
		 		:fileid::int,
		 		NULL::int)}
	set ltags [dbi_rows -db [ns_session get companyname] $sql]

	append html "<TABLE><TR>\n"
	append html "<TD><span class=\"label\">File:</span></TD><TD>\n"
	if {[string length $parentfileid]} {
		append html "<a href=/file/show?fileid=$parentfileid><- </a>"
	}
	append html "<a href=/file/get?fileid=$fileid>$filename</a> "
	if {[string length $childfileid]} {
		append html "<a href=/file/show?fileid=$childfileid> -></a>"
	}
	append html "</TD></TR>\n"
	append html "<TD><span class=\"label\">File Type:</span></TD>\n"
	append html "<TD>$filetypename</TD></TR>\n"
	append html "<TD><span class=\"label\">File Date:</span></TD>\n"
	append html "<TD>$filedate</TD></TR>\n"
	append html "<TD><span class=\"label\">Scanned By:</span></TD>\n"
	append html "<TD>$username</TD></TR>\n"
	append html "<TD><span class=\"label\">Scan Date:</span></TD>\n"
	append html "<TD>$scandate</TD></TR>\n"
	append html "<TD><span class=\"label\">Tags: "
	if {[hasrole "Metadata"]} {
		append html "<a href=/file/tag/new?fileid=${fileid}>(Add)</a>"
	}
	append html "</span></TD>\n<TD>"
	foreach {filetagid tagname} $ltags {

		if {[hasrole "Metadata"]} {
			append html "<a href=/file/tag/delete?filetagid=$filetagid>$tagname</a>\n"
		} else {
			append html "$tagname "
		}
	}
	append html "</TD></TR>\n"
	append html "</TABLE>\n"
	append html "<TABLE>\n"

	append html "<TR id=showocrdata><TD><a href=# onclick='document.getElementById(\"ocrdata\").style.display=\"table-row\"; "
	append html "document.getElementById(\"showocrdata\").style.display=\"none\";'>Show OCR</a></TD></TR>\n"
	append html "<TR id=ocrdata class=\"invisible\">\n"
	append html "<TD><a href=# onclick='document.getElementById(\"ocrdata\").style.display=\"none\"; "
	append html "document.getElementById(\"showocrdata\").style.display=\"table-row\";'>Hide OCR</a><HR><PRE>$ocrdata</PRE><HR></TD></TR>\n"

	append html "<TR id=showpreview><TD><a href=# onclick='document.getElementById(\"preview\").style.display=\"table-row\"; "
	append html "document.getElementById(\"showpreview\").style.display=\"none\";'>Show Preview</a></TD></TR>\n"
	append html "<TR id=preview class=\"invisible\">\n"
	append html "<TD><a href=# onclick='document.getElementById(\"preview\").style.display=\"none\"; "
	append html "document.getElementById(\"showpreview\").style.display=\"table-row\";'>Hide Preview</a><HR>\n"
	getfilelinks $fileid
	append html "<HR></TD></TR>\n"

	append html "</TABLE>\n"

	endpage

	ns_return 200 text/html $html
}

proc getfilelinks { fileid } {

	upvar html html
	set userid [ns_session get userid]
	set pagenum 0

	dbi_1row -db [ns_session get companyname] {SELECT filename, subpath 
		  FROM fn_file(:userid::int, 
				:fileid::int)}

	if {[file exists [file rootname [makepath ${filename} "" jpg]]-${pagenum}.jpg]} {
		# Thumbnails exist
		foreach jpgfile [glob [makepath ${filename}-*.jpg]] {
			append html "<img src=/file/get?fileid=$fileid&pagenum=${pagenum}>\n"
			incr pagenum
		}
	} else {
		append html "<IFRAME src=/file/get?fileid=$fileid WIDTH=100% HEIGHT=500px>Sorry.</IFRAME>\n"
	}
}

proc getfile {} {

	set fileid [safequeryget fileid integer]
	set pagenum [safequeryget pagenum integer -1]
	set userid [ns_session get userid]
	dbi_1row -db [ns_session get companyname] {SELECT filename, subpath 
		  FROM fn_file(:userid::int, 
				:fileid::int)}

	if {$pagenum >= 0} {
		# jpg link
		ns_returnfile 200 image/jpg [file rootname [makepath ${filename} "" jpg]]-${pagenum}.jpg
		return
	}
	if {[file exists [makepath ${filename}.pdf]]} {
		dbi_1row -db [ns_session get companyname] {SELECT fn_insertview(:userid::int, 
						:fileid::int) AS result}
		ns_returnfile 200 application/pdf [makepath ${filename}.pdf]
		return
	} else {
		ns_return 200 text/html "File [makepath ${filename}.pdf] not found"
		return
	}
}

proc startpage {title {scriptfile {}}} {
	upvar html html

	append html "<HTML>
<HEAD>
<TITLE>$title</TITLE>
"
css
if {[string length $scriptfile]} {
	append html [$scriptfile]
}
append html "
</HEAD>
<BODY>
"
	menu
	if {[ns_session contains errorstring]} {
		append html "<span class=errortext>[ns_session get errorstring]</span><BR>\n"
		ns_session delete errorstring
	}
	if {[ns_session contains messagestring]} {
		append html "<span class=messagetext>[ns_session get messagestring]</span><BR>\n"
		ns_session delete messagestring
	}
}

proc startform {{formname {}} {method POST} {action {}} {enctype {}}} {

	upvar html html
	append html "<FORM NAME=\"$formname\" "
	if {[string length $enctype]} {
		append html "ENCTYPE=multipart/form-data "
	}
	append html "METHOD=$method ACTION=\"$action\">\n"

}

proc selectbox {widgetname listname keycol displaycol {setvalue 1}} {
	# Generates a select widget.  
	# looks uplevel for the value of widgetname to set value as selected 
	# if setvalue is 1

	upvar $listname thelist
	upvar html html
	upvar $widgetname $widgetname

	append html "<SELECT NAME=$widgetname>\n"
	append html "<OPTION VALUE=\"\">-- Select One --\n"

	foreach "keycol displaycol" $thelist {
		append html "<OPTION VALUE=$keycol"
		if {$setvalue && 
		    [info exists $widgetname] &&
		    [string match [set $widgetname] $keycol]} {
			append html " SELECTED"
		}
		append html ">$displaycol</OPTION>\n"
	}

	append html "</SELECT>\n"
}

proc endform {} {

	upvar html html
	append html "<INPUT TYPE=submit>\n"
	append html "</FORM>\n"

}

proc endpage {}  {

	upvar html html
	append html "</BODY>\n</HTML>\n"

}

proc acquiredoc {docsource doctype {date ""} {duplicate 0} {dupfile ""}} {

	logerror Debug "Entering acquiredoc with document source $docsource type $doctype date $date and duplicate $duplicate and dupfile $dupfile"

	#  docsource - scanner, upload or robot
	#  doctype - type of document
	#  date - if not, use today's
	#  duplicate - duplicate option 0 = none, 1 = Replace, 2 = Make Unique, 3 = Append

	# Maybe here is where we look uplevel for local variables that
	# override the filetype scanoptions 

	upvar duplex duplex
	upvar mode mode
	upvar contrast contrast
	upvar brightness brightness
	upvar newfilename newfilename
	upvar companyname companyname

	set ext .pnm
	file mkdir [file join [nsv_get config rootpath] $companyname]
	cd [file join [nsv_get config rootpath] $companyname]

	if {[string match "scan" $docsource]} {
		if {[string match "t" $duplex]} {
			set source "ADF Duplex"
		} else {
			set source "ADF Front"
		}
	}

	if {![string length $date]} {
		set date [clock format [clock seconds] -format %Y%m%d]
	}

	set fileroot [ string toupper ${doctype}_${date} ]
	
	# clean up from failed previous run.

	catch {eval exec rm [glob ${fileroot}*]} errMsg
	logerror Notice "When cleaning up - $errMsg"

	# Check for existing file

	if {[file exists [makepath ${fileroot}.pdf]]} {
		
		switch $duplicate {
			0 {
				# Found duplicate, preference not sent, so return error
				return -code error "File [makepath ${fileroot}.pdf] exists"
			}
			1 {
				# For replace I need to call moveoldfile

				if {[moveoldfile $fileroot]} {
					set fileroot $dupfile
				} else {
					return -code error "Unable to move file"
				}
				logerror Debug "After moveoldfile newfilename is $newfilename"
				}
			3 {
				# append
				set fileroot $dupfile
				# set newfilename $dupfile; This seems odd.
			}
			2 {
				# Make unique.  
				set fileroot ${doctype}_${date}_[gettiebreaker]
			}
			default {
				return -code error "Something bad."
			}
		}
	}

	# Scan documents and check return code

	if {[string match "scan" $docsource]} {

		logerror Debug "Scanning file $fileroot"

		logerror Debug "exec scanadf -o ${fileroot}_%d${ext} --brightness $brightness --resolution 300 --source $source --mode $mode errmsg"
		catch {exec scanadf -o ${fileroot}_%d${ext} --contrast $contrast --brightness $brightness --resolution 300 --source $source --mode $mode} errmsg
		logerror Debug $errmsg

		if {[string match $errmsg "scanadf: no SANE devices found"]} {
			return -code error "Error! $errmsg"
		}

		# Convert to tif

		foreach file [lsort [glob ${fileroot}*]] {
			exec pnmtotiff $file > ${file}.tif
			lappend filelist "${file}.tif"
		}

		# combine them and
		# Convert to pdf
	}

	if {[string match "robot" $docsource]} {

		# Move the file(s) to the work directory.
		# Only tif right now.  This should move more 
		# than one if they exist, but things are too spaghetti 
		# to figure it out right now.   

		set i 1
		if [catch {set thefiles [lsort [glob $newfilename]]} errMsg] {

			ns_log Notice "File $newfilename nonexistent"
			return -code error 0
		}

		foreach file $thefiles {
			if {[catch {exec cp $file ${fileroot}.${i}.tif} errMsg]} {
				ns_log Notice "Error copying file: $errMsg"
				return -code error 0
			} 
			lappend filelist ${fileroot}.${i}.tif
			incr i
		}

	}

	if {[string match "robot" $docsource] ||
            [string match "scan" $docsource]} {

		switch $duplicate {
			0 -
			1 -
			2 {
				# Not duplicate, replace or already made unique

				if [catch {eval exec tiffcp $filelist [makepath ${fileroot}.tif]} errmsg] {
					logerror Notice "tiffcp produced an error = $errmsg"
				}
				exec tiff2pdf [makepath ${fileroot}.tif] > [makepath ${fileroot}.pdf]
				} 
			3 {
				# Append
				# If there is a tif file, append the tif and flag the record
				# the db record will get the entire thing OCRd again.

				if {[file exists [makepath ${fileroot}.tif]]} {
					if [catch {eval exec tiffcp [linsert $filelist 0 \
					[makepath ${fileroot}.tif]] [makepath ${fileroot}.tif].new} errmsg] {
						logerror Notice "tiffcp produced an error - $errmsg"
					}

					eval exec mv [makepath ${fileroot}.tif].new \
					[makepath ${fileroot}.tif]

					exec tiff2pdf [makepath ${fileroot}.tif] \
					> [makepath ${fileroot}.pdf]
					
				} else {

					# If there is not a tif file, it has been deleted 
					# or never existed so we have to
					# append the tif.  Flag the DB record to pick up 
					# the old index if any
					# in addition to the new index data.
					# If this works, we could append uploaded files...

					if [catch {eval exec tiffcp $filelist [makepath ${fileroot}.tif]} errmsg] {
						logerror Notice "tiffcp produced an error = $errmsg"
					}

					exec tiff2pdf [makepath ${fileroot}.tif] \
					> [makepath ${fileroot}.pdf].new

					exec pdftk [makepath ${fileroot}.pdf] \
					[makepath ${fileroot}.pdf].new cat \
					output [makepath ${fileroot}.pdf].tmp
					
					# I think we cant combine into one of the inputs

					exec mv [makepath ${fileroot}.pdf].tmp \
					[makepath ${fileroot}.pdf]

					# Now, about the index... Flagging as ocrcomplete
					# FALSE while ocrdata exists should be a flag to
					# append the old stuff to the new.  Not sure where
					# when or how to update the record...

				}
				}
			default {
				# return error
				}
			}


		catch {eval exec rm [glob ${fileroot}*]} errMsg
		return $fileroot

	} elseif {[string match upload $docsource]} {

		logerror Debug "Upload file...$fileroot"

		set ext [uploadfile $fileroot] 
		if {[string match "tif" $ext]} {
			exec tiff2pdf [makepath ${fileroot}.tif] > [makepath ${fileroot}.pdf]
		}
			
		return $fileroot

	} else {
		seterrorstring "Unknown document source $docsource"
	}
	return -code error
}

proc gettiebreaker {} {

	upvar companyname companyname
	dbi_1row -db $companyname {SELECT nextval ('filetiebreaker_seq') as retval}
	return $retval
}

proc writefiletodb {filename {duplicate 0}} {

	set userid [ns_session get userid]

	if {$duplicate == 1 || $duplicate == 3} {
		# This is a replace or append, we need parent file
		set replace 1
	} else {
		# This is make unique or already unique.  No parent needed
		set replace 0
	}

	set varlist {date filetypename tag1 tag2 tag3 tag4 tag5 brightness contrast mode duplex newfilename docsource}

	foreach var $varlist {
		upvar $var $var
		logerror Debug [set $var]
	}

	set filetypename [safefilename $filetypename]
	set newfilename [safefilename $newfilename]

	dbi_1row -db [ns_session get companyname] {SELECT fn_checkfiletype(:filetypename::varchar,
			 fn_checkscanoption(:brightness::int, :contrast::int, 
			 :mode::varchar, :duplex::boolean)) AS filetypeid}

	# Call makepath to instantiate subpath in this context
	makepath $filename

	dbi_1row -db [ns_session get companyname] {SELECT fn_insertfile(:userid::int, 
					:filename::varchar, 
					:date::date, 
					:filetypeid::int, 
					:tag1::varchar, 
					:tag2::varchar, 
					:tag3::varchar, 
					:tag4::varchar, 
					:tag5::varchar, 
					:replace::boolean, 
					:newfilename::varchar,
					:subpath::varchar,
					:docsource::varchar) as result}
	
	return $result
}

proc safefilename {input} {

	regsub -all {[^A-Za-z0-9_]} $input {_} retval
	return $retval

}

proc safeinteger {input} {
	if {[string is integer $input]} {
		return $input
	} else {
		return ""
	}
}

proc safequeryget {varname datatype {defaultval ""}} {

	set tmpvar [ns_queryget $varname $defaultval]
	if {[string is $datatype -strict $tmpvar]} {
		return $tmpvar
	} 
	# for the datatype, scrub out non valid characters
	case $datatype {
		"wordchar" {
			regsub -all { } $tmpvar {_} tmpvar
			regsub -all {[^A-Za-z0-9_]} $tmpvar {} tmpvar
			return $tmpvar
			}
		"alnum" {
			regsub -all {[^A-Za-z0-9]} $tmpvar {} tmpvar
			return $tmpvar
			}
		"alpha" {
			regsub -all {[^A-Za-z]} $tmpvar {} tmpvar
			return $tmpvar
			}
		"integer" {
			regsub -all {[^0-9]} $tmpvar {} tmpvar
			return $tmpvar
			}
	}
	return ""	
}

proc loadconfig {{reload 0}} {

	if {![nsv_exists config configid] ||
	     $reload} {
		dbi_1row -db dbadminpg {
			SELECT configid, rootpath, pwstrength, pwage, maxfaillogin,
			lockout, inactivelock, auditretain, loglevel, keeptiff
			FROM config
			}
		foreach var {configid rootpath pwstrength pwage maxfaillogin lockout inactivelock auditretain loglevel} { 
			nsv_set config $var [set $var]
		}
	}
}

loadconfig

ns_schedule_proc -thread 60 ocrdaemon 

proc ocrdaemon {} {

	# Looks at new file records and OCRs their tif files.
	# Writes results to a txt file and to the database where they are 
	# full text indexed.

	set sql {SELECT companyname FROM company}
	set companylist [dbi_rows -db dbadminpg $sql]

	foreach companyname $companylist {

		set sql {/* ocrdaemon */ 
			SELECT fileid, filepath, subpath, filename 
			FROM file f JOIN filetype ft
			ON f.filetypeid = ft.filetypeid 
			WHERE ocrdata IS NULL AND ocr AND NOT EXISTS (select * from file where parentfileid = f.fileid)}
		set lfiles [dbi_rows -db $companyname $sql]

		foreach {fileid filepath subpath filename} $lfiles {
		
			set rootpath [file join $filepath $companyname]
			set tifname [file join $rootpath $subpath tif ${filename}.tif]
			set pdfname [file join $rootpath $subpath pdf ${filename}.pdf]
			set txtname [file join $rootpath $subpath txt ${filename}.txt]
			set jpgname [file join $rootpath $subpath jpg ${filename}.jpg]

			# If this is a tif file run ocr.

			logerror Debug "looking for $filename in $filepath as $tifname"

			# Since I'm not using makepath anymore (it did
			# not take into account suffixes on makeunique)
			# I have to make sure the txt folder exists.
			file mkdir [file dirname $txtname]
			file mkdir [file dirname $jpgname]

			if {[file exists $tifname]} {

				# Seems like a good time to make thumbnails...

				logerror Debug "Thumbnailing file $tifname"

				catch {exec convert -resize 250 $tifname $jpgname} errMsg
				logerror Debug $errMsg	

				logerror Debug "Recognizing file $tifname"

				catch {exec /usr/bin/tesseract $tifname \
					[file root $txtname]} errMsg
				logerror Debug $errMsg

			} elseif {[file exists $pdfname]} {
		
			# If its a pdf with no tif, run pdftotext

				logerror Debug "Stripping text from $pdfname"

				catch {exec /usr/bin/pdftotext $pdfname $txtname} errMsg
				logerror Notice $errMsg
			}

			# if everything is ok

			if {[file exists $txtname]} {

				logerror Debug "Found text file $txtname"

				set fp [open $txtname]
				set ocrtext [read $fp]
				#regsub -all {[^A-Za-z0-9 ]} $ocrtext {} ocrtext

				dbi_dml -db $companyname {/* ocrdaemon */ 
					UPDATE file 
					SET ocrdata = :ocrtext 
					WHERE fileid = :fileid}
			} else {
				dbi_dml -db $companyname {/* ocrdaemon */ 
					UPDATE file 
					SET ocrdata = '' 
					WHERE fileid = :fileid}
			}
		}
	}
}

proc lookuptag {} {

	set tagname [safequeryget tagname wordchar]
	set found [dbi_0or1row -db [ns_session get companyname] \
		 {SELECT tagname as retval
		  FROM tag 
		  WHERE tagname ilike :tagname || '%'
		  ORDER BY tagname LIMIT 1}]
	if {!$found} {
		ns_return 200 text/html ""
		return
	}
	ns_return 200 text/html $retval
	return
}

proc lookupfiletype {} {

	set userid [ns_session get userid]
	set classificationid [ns_session get classificationid]
	set filetypename [safequeryget filetypename wordchar]

	set found [dbi_0or1row -db [ns_session get companyname] \
		{SELECT filetypename as retval
		FROM fn_filetypes (:userid::int4,
					NULL::int4,
					:classificationid::int4)
		WHERE filetypename ilike :filetypename || '%'
		ORDER BY filetypename LIMIT 1}] 
	if {!$found} {
		ns_return 200 text/html ""
		return
	}
	ns_return 200 text/html $retval
}

proc random {{range 100}} {
    return [expr {int(rand()*$range)}]
}

proc jsscannew {} {

set html "
<SCRIPT TYPE=text/javascript>

var xmlHttp;

function ajaxGetFileType(str)
{
if (str.length==0)
  { 
  document.getElementById(\"filetypenametemp\").innerHTML=\"\";
  return;
  }
xmlHttp=new XMLHttpRequest();
xmlHttp.onreadystatechange=function()
  {
  if(xmlHttp.readyState==4)
    {
    document.getElementById(\"filetypenametemp\").innerHTML=xmlHttp.responseText;
    }
  }
var url=\"/lookupfiletype\";
url=url+\"?filetypename=\"+str;
url=url+\"&sid=\"+Math.random();
xmlHttp.onreadystatechange=ajaxUpdateFileType;
xmlHttp.open(\"GET\",url,true);
xmlHttp.send(null);
}

function ajaxUpdateFileType()
{
  if (xmlHttp.readyState==4)
  { 
    document.getElementById(\"filetypenametemp\").innerHTML=xmlHttp.responseText;
  }
}

function ajaxSetFileType()
{
  if (document.getElementById(\"filetypenametemp\").innerHTML!=\"\")
  {
    document.newscanform.filetypename.value=document.getElementById(\"filetypenametemp\").innerHTML;
    document.getElementById(\"filetypenametemp\").innerHTML=\"\"
  }
}
"

for {set i 1} {$i < 6} {incr i} {
	append html " 

function ajaxGetTagName${i}(str)
{
if (str.length==0)
  { 
  document.getElementById(\"tagtemp$i\").innerHTML=\"\";
  return;
  }
xmlHttp=new XMLHttpRequest();
xmlHttp.onreadystatechange=function()
  {
  if(xmlHttp.readyState==4)
    {
    document.getElementById(\"tagtemp$i\").innerHTML=xmlHttp.responseText;
    }
  }
var url=\"/lookuptag\";
url=url+\"?tagname=\"+str;
url=url+\"&sid=\"+Math.random();
xmlHttp.onreadystatechange=ajaxUpdateTagName$i;
xmlHttp.open(\"GET\",url,true);
xmlHttp.send(null);
}

function ajaxUpdateTagName${i}()
{
  if (xmlHttp.readyState==4)
  { 
    document.getElementById(\"tagtemp$i\").innerHTML=xmlHttp.responseText;
  }
}

function ajaxSetTagName${i}()
{
  if (document.getElementById(\"tagtemp$i\").innerHTML!=\"\")
  {
    document.newscanform.tag$i.value=document.getElementById(\"tagtemp$i\").innerHTML;
    document.getElementById(\"row[expr $i + 1]\").style.display=\"table-row\";
    document.newscanform.tag[expr $i + 1].focus();
    document.getElementById(\"tagtemp$i\").innerHTML=\"\";
  }
}
"
} 

append html "

function showScanOptions() 
{
  document.getElementById(\"row6\").style.display=\"table-row\";
  document.getElementById(\"row7\").style.display=\"table-row\";
  document.getElementById(\"row8\").style.display=\"table-row\";
  document.getElementById(\"row9\").style.display=\"table-row\";
  document.getElementById(\"rowHideOptions\").style.display=\"table-row\";
  document.getElementById(\"rowShowOptions\").style.display=\"none\";
}

function hideScanOptions() 
{
  document.getElementById(\"row6\").style.display=\"none\";
  document.getElementById(\"row7\").style.display=\"none\";
  document.getElementById(\"row8\").style.display=\"none\";
  document.getElementById(\"row9\").style.display=\"none\";
  document.getElementById(\"rowShowOptions\").style.display=\"table-row\";
  document.getElementById(\"rowHideOptions\").style.display=\"none\";
  
}
"
append html "</SCRIPT>
"
}

proc css {} {

upvar html html
append html "
<STYLE TYPE=text/css>
.label {
	font-weight: bold;
}
.messagetext {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 12px;
	color: #0000FF;
}
.errortext {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 12px;
	color: #FF0000;
}
tr.invisible {
	display: none;
}
tr.visible {
	display: table-row;
}
td {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 12px;
	color: #000000;
	vertical-align: top;
}
h1 {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 14px;
	color: #000099;
}
body {
	margin-left: 10%;
}
a {
	color: #009900;
}
a:link {
	color: #000099;
	text-decoration: underline;
}
a:visited {
	color: #666666;
	text-decoration: none;

}
a:hover {
	text-decoration: none;
	background-color: #CCCCCC;
}
a:active {
	text-decoration: none;

}

.menu {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 11px;
	color: #000000;
}
.menu a:link {
	color: #336600;
	text-decoration: none;
}
.menu a:visited {
	color: #666666;
	text-decoration: none;
}

.menu a:hover {
	color: #990000;
	text-decoration: none;
}

.menu a:active {
	color: #666666;
	text-decoration: none;
}
</STYLE>
"
}

proc menu {} {

	# context sensitive menu generator

	upvar html html

	append html "<span class=menu>\n"
	if {[ns_session contains userid]} {
		append html "| <a href=/logout>Log out</a> | \n"
	}
	if {[ns_session contains Admin]} {	
		append html "<a href=/admin>Admin</a> | \n"
	}
	if {[ns_session contains Scanner]} {	
		append html "<a href=/acquire/scan>Scan</a> | \n"
	}
	if {[ns_session contains Upload]} {	
		append html "<a href=/acquire/upload>Upload</a> | \n"
	}
	if {[ns_session contains Viewer]} {
		append html "<a href=/file/browse>Browse</a> | \n"
		append html "<a href=/file/search>Search</a> | \n"
	}
	append html "[ns_session get username] [ns_session get classification] "
	append html "[ns_session get companyname]"
	append html "</span><BR>\n"
}

proc setauth {userid} {

	# get user authorization and set session variables

	set i 0
	set sql {SELECT rolename 
		    FROM fn_userroles(:userid::int, NULL::int)}
	foreach rolename [dbi_rows -db [ns_session get companyname] $sql] {
		ns_session put $rolename 1
		incr i
	}
	return $i
	
}

proc setmessagestring {string} {
	ns_session put messagestring "[ns_session get messagestring]<br> $string"
}

proc seterrorstring {string} {
	ns_session put errorstring "[ns_session get errorstring]<br> $string"
}

proc moveoldfile {filename} {

	# When a file is replaced, move the old file out of the way
	# Throws new filename uplevel
	
	set userid [ns_session get userid]
	upvar newfilename newfilename

	set newfilename ${filename}[gettiebreaker]
	foreach ext {tif pdf txt} {
		if {[file exists [makepath ${filename}.${ext}]]} {
			exec mv [makepath ${filename}.${ext}] \
			[makepath ${newfilename}.${ext}]
		} else {
			logerror Debug "File not found for moveoldfile: \
			[makepath ${filename}.${ext}]"
			setmessagestring "File not found for moveoldfile $ext"
		}
	}
	if {[catch {dbi_1row -db [ns_session get companyname] {select fn_updatefilename(:userid, 
		:filename, :newfilename) as result}} errMsg]} {
		seterrorstring $errMsg
		return 0
	}
	if {![info exists result]} {
		seterrorstring "Error updating file name for parent file"
		return 0 
	}
	return 1
}

proc tifcompressed { file } {
	# See if the tif file is compressed.

	set comp [exec tiffinfo $file]
	if {[string match "*Compression Scheme: None*" $comp]} {
		return 0
	}
	return 1
}

proc uncompresstif { file } {
	# Uncompress the given tif file
	
	if {[catch {exec tiffcp -c none $file ${file}_u} errMsg]} {
		logerror Error "Error decompressing file $errMsg"
		return -code error
	}
	exec mv ${file}_u $file
}	

proc makepath {filename {rootpath ""} {ext ""}} {

	# Takes a rootpath and filename with extension, and makes a full path
	# making sure the path exists along the way.  Returns full file path and name
	# Checks for existence of subpath variable uplevel and if found uses it 
	# to assemble the path.

	# Looks for companyname uplevel so path can be built for robots.

	upvar subpath subpath
	upvar companyname companyname

	if {![info exists companyname]} {
		set companyname [ns_session get companyname]
	}

	if {![string length $rootpath]} {
		set rootpath [file join [nsv_get config rootpath] $companyname]
	}

	if {![string length $ext]} {
		set ext [string trim [file extension $filename] "."]
	} else {
		set filename ${filename}.${ext}
	}
		
	set fileroot [file rootname $filename]
	if {![info exists subpath]} {
		set subpath [string trimleft [ns_hashpath [ns_md5 $fileroot] 3] "/"]
	}
	set fullpath [file join $rootpath $subpath $ext]
	file mkdir $fullpath
	return [file join $fullpath ${filename}]
}

proc completeworkflowstep {} {

	# mark a file as having a given users workflow step complete.

	set userid [ns_session get userid integer]
	set fileid [safequeryget fileid integer]
	set workflowstepid [safequeryget workflowstepid integer]

	if {[string match POST [ns_conn method]]} {
		
		set sql {SELECT fn_completeworkflowstep(:userid::int,
				:workflowstepid::int,
				:fileid::int) as result}
		dbi_1row -db [ns_session get companyname] $sql
		if {!$result} {
			seterrorstring "Unable to complete workflow step"
		}
		ns_returnredirect "/"
		return
	}
	
	set sql {SELECT workflowtype, filename 
		 FROM fn_workflowpendings(:userid::int,
					 :workflowstepid::int)
		 WHERE fileid = :fileid}
	dbi_1row -db [ns_session get companyname] $sql

	startpage "Complete Workflow?"
	startform formConfirm

	append html "<TABLE><TR>\n"
	append html "<TD><span class=\"label\">Complete workflow \"$workflowtype\" "
	append html "for file \"$filename\"?</span>"
	append html "</TD></TR>\n"
	append html "<INPUT TYPE=hidden NAME=fileid VALUE=$fileid>\n"
	append html "<INPUT TYPE=hidden NAME=workflowstepid VALUE=$workflowstepid>\n"
	
	endform
	endpage

	ns_return 200 text/html $html
}

proc scanfolderindex {} {

	set html {}

	set userid [ns_session get userid]

	startpage "Scan Folders"
	append html "<TABLE><TR><TD><span class=\"label\">Scan Folders"
	append html "<a href=/admin/scanfolder/new>(New)</a></span></TD><TD>\n"

	set sql {SELECT scanfolderid, path, case when active then '<B>' else '' end as starttag,
		case when active then '</B>' else '' end as endtag
		FROM fn_scanfolders(:userid::integer,
				  null::integer)}
	set template {$starttag <a href=/admin/scanfolder/update?scanfolderid=$scanfolderid>$path</a> $endtag <br>}
	append template "\n"
	set default {No scan folders exist yet.}
	append html [dbi_rows -db [ns_session get companyname] $sql $template $default]

	append html "</TD></TR></TABLE>\n"

	endpage
	ns_return 200 text/html $html
}

proc updscanfolder {} {

 	set varlist [list scanfolderid int path ascii filetypeid int namepattern ascii dateregex ascii \
		 active ascii duplicate int tag1 ascii tag2 ascii tag3 ascii tag4 ascii tag5 ascii]
	foreach {var vartype} $varlist {
		set $var [safequeryget $var $vartype]
puts "$var is [set $var]"
	}

	set adminuserid [ns_session get userid]

	if {[string match POST [ns_conn method]]} {

		set sql {SELECT fn_scanfolder(:adminuserid::int,
				:scanfolderid::int,
				:path::varchar, 
			    	:filetypeid::int,
				:namepattern::varchar,
				:dateregex::varchar,
				:active::boolean,
				:duplicate::int,
				:tag1::varchar,
				:tag2::varchar,
				:tag3::varchar,
				:tag4::varchar,
				:tag5::varchar
				) AS scanfolderid}

		dbi_1row -db [ns_session get companyname] $sql
		
		if {$scanfolderid > 0} {
			ns_returnredirect /admin/scanfolder
		} else {
			seterrorstring "Error updating scanfolder"
		}  
	} 

	set html {}
	startpage "Edit Scanfolder"
	startform editScanfolderForm
	set adminuserid [ns_session get userid]

	dbi_1row -db [ns_session get companyname] {SELECT * 
		 FROM fn_scanfolders(:adminuserid::int,
				:scanfolderid::int)}



	foreach {var vartype} $varlist {
puts "$var is NOW... [set $var]"
	}


	append html "<TABLE><TR><TD><span class=\"label\"> Path: </span> \n"
	append html "</TD><TD><INPUT TYPE=TEXT NAME=path VALUE=\"$path\"></TD></TR>\n"
	append html "<TR><TD><span class=\"label\"> File Name Pattern: </span> \n"
	append html "</TD><TD><INPUT TYPE=TEXT NAME=namepattern VALUE=$namepattern></TD></TR>\n"
	append html "<TR><TD><span class=\"label\"> Date Expression: </span> \n"
	append html "</TD><TD><INPUT TYPE=TEXT NAME=dateregex VALUE=$dateregex></TD></TR>\n"
	append html "<TR><TD><span class=\"label\"> File Type: </span> \n </TD><TD>"

	set sql {SELECT filetypeid, filetypename
		 FROM fn_filetypes(:adminuserid::int, null::int, null::int)
		 ORDER BY filetypename}
        set lfiletype [dbi_rows -db [ns_session get companyname] $sql]

        selectbox filetypeid lfiletype filetypeid filetypename

	append html "</TD></TR>\n"

	append html "<TR><TD><span class=\"label\"> Enabled: </TD>\n"
	append html "<TD><SELECT NAME=active>\n"
	append html "<OPTION VALUE=\"t\""
	if {[string match "t" $active]} {
		append html " SELECTED"
	}
	append html ">Yes</OPTION>\n"
	append html "<OPTION VALUE=\"f\""
	if {[string match "f" $active]} {
		append html " SELECTED"
	}
	append html ">No</OPTION>\n"
	append html "</SELECT></TD></TR>\n"
	
	append html "<TR><TD><span class=\"label\"> Duplicate Option: </TD>\n"
	append html "<TD><SELECT NAME=duplicate>\n"
	append html "<OPTION VALUE=\"2\""
	if {[string match "2" $duplicate]} {
		append html " SELECTED"
	}
	append html ">Make Unique</OPTION>\n"
	append html "<OPTION VALUE=\"3\""
	if {[string match "3" $duplicate]} {
		append html " SELECTED"
	}
	append html ">Append</OPTION>\n"
	append html "</SELECT></TD></TR>\n"

	set sql {SELECT tagname as tagid, tagname
		 FROM tag
		 WHERE NOT workflowtag
		 ORDER BY tagname}
	set ltagname [dbi_rows -db [ns_session get companyname] $sql]

	for {set i 1} {$i < 6} {incr i} {
		append html "<TR><TD><span class=\"label\"> Tag $i: </span> \n </TD><TD>"
		selectbox tag$i ltagname tagid tagname
		append html "</TD></TR>\n"
	}

	append html "</TABLE>\n"
	append html "<INPUT TYPE=hidden NAME=scanfolderid VALUE=$scanfolderid>\n"

	endform
	endpage
	ns_return 200 text/html $html
}

proc logerror {errlevel {logmessage ""}} {

	# consistent error logging based on debug setting
	# Error level Error,Warning,Notice and Debug

	set loglevel [nsv_get config loglevel]
	array set loglevels [list Debug 1 Notice 2 Warning 3 Error 4]
	
	if {$loglevels($errlevel) < $loglevels($loglevel)} {
		# Dont log
		return
	}

	ns_log $errlevel "$logmessage in [info level 1]"
}

proc cleandaemon {} {

	# Looks at files older than the keeptiff setting and deletes them.

	set sql {SELECT companyname FROM company}
	set companylist [dbi_rows -db dbadminpg $sql]
	set keeptiff [nsv_get config keeptiff]

	foreach company $companylist {

		set sql "/* cleandaemon */ 
			SELECT fileid, filepath, filename 
			FROM $company.file f JOIN company.filetype ft
			ON f.filetypeid = ft.filetypeid 
			WHERE hastiff and scandate + :keeptiff::interval
			< current_date"
		set lfiles [dbi_rows -db dbadminpg $sql]

		set sql "/*cleandaemon*/
			UPDATE $company.file
			SET hastiff = false
			WHERE fileid = :fileid::integer"

		foreach {fileid filepath filename} $lfiles {

			if [catch {rm [file join filepath filename].tif} errMsg] {
				logerror Debug "File $filename not found for tiff delete"
			} else {
				logerror Debug "Deleted $filename ..."
			}

			dbi_dml -db dbadminpg $sql

		}
	}
}

proc scanfolder {} {

	set sql {SELECT companyname FROM company}
	set companylist [dbi_rows -db dbadminpg $sql]

	foreach companyname $companylist {

		# Set default pool handle to match so gettiebreaker will work from within acquiredoc.

		# Get list of folders to scan

		set sql {SELECT  scanfolderid, path, filetypename, namepattern, }
		append sql {dateregex, filetypeid, duplicate, tag1, tag2, tag3, tag4, tag5 FROM vw_scanfolder sf }
		append sql {WHERE active}
		set folderlist [dbi_rows -db $companyname $sql]

		foreach {scanfolderid path filetypename namepattern dateregex filetypeid duplicate tag1 tag2 tag3 tag4 tag5} $folderlist {
			# glob all the files
			
			logerror Debug "Looking for files matching $namepattern in $path ..."
			set thefiles [lsort [glob -nocomplain -directory $path $namepattern]]
			logerror Debug "The files are $thefiles"
			set cnt 0

			foreach newfilename $thefiles {

				incr cnt
				logerror Debug "Found $newfilename ..."

				# extract the dates

				set datefound [regexp $dateregex $newfilename trash date]
				if {!$datefound || [catch {clock scan $date}]} {
					set date [clock format [file mtime $newfilename] -format "%Y%m%d"]
					logerror Debug "No date found using regexp, using $date from mtime."
				} else {
					logerror Debug "regex returned $datefound with date $date"
				}

				logerror Debug "Using date $date ..."
		
				# acquire the document

				set dupfile ""
				set replace 0

				if [catch {set filename [acquiredoc robot $filetypename $date]} errmsg] {
					logerror Debug "First attempt as new file for $newfilename failed.  Trying as duplicate"

					if {[string match File*exists $errmsg]} {
						# file exists, need to try again with duplicate flag

						if {$duplicate == 3} {
							# Append existing file

							set dupfile "${filetypename}_${date}"
							set replace 1
						}

						if [catch {set filename [acquiredoc robot $filetypename $date $duplicate $dupfile]} errmsg] {
							# Something is really wrong.
							logerror Notice "Unable to slurp $newfilename with duplicate = $duplicate - $errmsg"
							continue
						}

					} else {
						# Some other error

						logerror Notice "Unable to slurp $newfilename - $errmsg"
						continue
					}
				} 
				# Insert into db
				makepath ${filetypename}_${date}
				dbi_1row -db $companyname {SELECT fn_insertfile(1::int, 
								:filename::varchar, 
								:date::date, 
								:filetypeid::int, 
								:tag1::varchar, 
								:tag2::varchar, 
								:tag3::varchar, 
								:tag4::varchar, 
								:tag5::varchar, 
								:replace::boolean, 
								:dupfile::varchar,
								:subpath::varchar,
								'robot'::varchar) as result}
				unset subpath

				# mark as done.  This will fail like crazy if the web user cant hack files in that dir.
				if [catch {file rename $newfilename ${newfilename}.done} errmsg] {
					logerror Notice "Unable to rename file ${newfilename}.  Check permissions on slurp folder."
					break
				}
			}
			
			# Update last run info
			set sql "UPDATE scanfolder SET lastrun = current_timestamp, lastruncount = :cnt"
			dbi_dml -db $companyname $sql
		}
	}
}
ns_schedule_proc 60 scanfolder
